PHPBuilder - Database Normalization and Design Techniques Page 6



RSS Twitter
Articles Databases

Database Normalization and Design Techniques - Page 6

by: Barry Wise
|
July 31, 2000

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

« Previous Page
1
|
2
|
3
|
4
|
5
|
6

Comment and Contribute

Your comment has been submitted and is pending approval.

Author:
Barry Wise

Comment:



Comment:

(Maximum characters: 1200). You have characters left.