Database Normalization And Design Techniques - Second Form

(Page 3 of 6 )

1. Create separate tables for sets of values that apply to multiple records.
2. Relate these tables with a foreign key.

We break the url values into a separate table so we can add more in the future without having to duplicate data.

We'll also want to use our primary key value to relate these fields:


Ok, we've created separate tables and the primary key in the users table, userId, is now related to the foreign key in the urls table, relUserId. We're in much better shape.

But what happens when we want to add another employee of company ABC? Or 200 employees? Now we've got company names and addresses duplicating themselves all over the place, a situation just rife for introducing errors into our data. So we'll want to look at applying the Third Normal Form.

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:


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 perfectly 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 coincidence 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.

Next: Data Relationships >>
 

More MySQL Articles
More By Barry Wise

Comments On: Database Normalization And Design Techniques

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