Database Normalization And Design Techniques - Fourth Normal Form

(Page 5 of 6 )

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.

Its 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.

Next: Conclusion >>
 

More MySQL Articles
More By Barry Wise

Comments On: Database Normalization And Design Techniques

>>> Be the FIRST to comment on this article!