Database Normalization and Design Techniques Page 6

Fourth Normal Form

1. In a many-to-many relationship, independent entities can not be stored in the same table.

Since it only applies to the many-to-many relationship, most developers can rightfully ignore this rule. But it does
come in handy in certain situations, such as this one. We’ve successfully streamlined our urls table to remove
duplicate entries and moved the relationships into their own table.

Just to give you a practical example, now we can select all of Joe’s urls by performing the following SQL call:

SELECT name, url FROM users, urls, url_relations WHERE url_relations.relatedUserId = 1 AND users.userId = 1 AND urls.urlId = url_relations.relatedUrlId

And if we wanted to loop through everybody’s User and Url information, we’d do something like this:

SELECT name, url FROM users, urls, url_relations WHERE users.userId = url_relations.relatedUserId AND urls.urlId = url_relations.relatedUrlId

Fifth Normal Form

There is one more form of normalization which is sometimes applied, but it is indeed very esoteric and is in most
cases probably not required to get the most functionality out of your data structure or application. It’s tenet suggests:

1. The original table must be reconstructed from the tables into which it has been broken down.

The benefit of applying this rule ensures you have not created any extraneous columns in your tables,
and that all of the table structures you have created are only as large as they need to be. It’s good practice to apply
this rule, but unless you’re dealing with a very large data schema you probably won’t need it.

I hope you have found this article useful, and are able to begin applying these rules of normalization to all of your
database projects. And in case you’re wondering where all of this came from, the first three rules of normalization
were outlined by Dr. E.F. Codd in his 1972 paper, “Further Normalization of the Data Base Relational Model”. Other rules
have since been theorized by later Set Theory and Relational Algebra mathematicians.

–Barry
itcn.com