PHPBuilder - Database Normalization and Design Techniques Page 4



RSS Twitter
Articles Databases

Database Normalization and Design Techniques - Page 4

by: Barry Wise
|
July 31, 2000

Third Normal Form

  1. Eliminate fields that do not depend on the key.

Our Company Name and Address have nothing to do with the User Id, so they should have their own Company Id:
users
userId name relCompId
1 Joe 1
2 Jill 2

companies
compId company company_address
1 ABC 1 Work Lane
2 XYZ 1 Job Street

urls
urlId relUserId url
1 1 abc.com
2 1 xyz.com
3 2 abc.com
4 2 xyz.com
Now we've got the primary key compId in the companies table related to the foreign key in the users table called relCompId, and we can add 200 users while still only inserting the name "ABC" once. Our users and urls tables can grow as large as they want without unnecessary duplication or corruption of data. Most developers will say the Third Normal Form is far enough, and our data schema could easily handle the load of an entire enterprise, and in most cases they would be correct.
But look at our url fields - do you notice the duplication of data? This is prefectly acceptable if we are not pre-defining these fields. If the HTML input page which our users are filling out to input this data allows a free-form text input there's nothing we can do about this, and it's just a coincedence that Joe and Jill both input the same bookmarks. But what if it's a drop-down menu which we know only allows those two urls, or maybe 20 or even more. We can take our database schema to the next level, the Fourth Form, one which many developers overlook because it depends on a very specific type of relationship, the many-to-many relationship, which we have not yet encountered in our application.

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

Comment and Contribute

Your comment has been submitted and is pending approval.

Author:
Barry Wise

Comment:



Comment:

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