Home arrow MySQL arrow Page 3 - Database Normalization And Design Techniques
MYSQL

Database Normalization And Design Techniques


What is database normalization and how does it apply to us? In this article Barry teaches us the best way to structure our database for typical situations.

Author Info:
By: Barry Wise
Rating: 5 stars5 stars5 stars5 stars5 stars / 57
January 09, 2003
TABLE OF CONTENTS:
  1. · Database Normalization And Design Techniques
  2. · Zero Form
  3. · Second Form
  4. · Data Relationships
  5. · Fourth Normal Form
  6. · Conclusion

print this article
SEARCH DEVARTICLES

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:

users
userIdnamecompanycompany_address
1JoeABC1 Work Lane
2JillXYZ1 Job Street

urls
urlIdrelUserIdurl
11abc.com
21xyz.com
32abc.com
42xyz.com

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:

users
userIdnamerelCompId
1Joe1
2Jill2

companies
compIdcompanycompany_address
1ABC1 Work Lane
2XYZ1 Job Street

urls
urlIdrelUserIdurl
11abc.com
21xyz.com
32abc.com
42xyz.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 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.
blog comments powered by Disqus
MYSQL ARTICLES

- MySQL and BLOBs
- Two Lessons in ASP and MySQL
- Lord Of The Strings Part 2
- Lord Of The Strings Part 1
- Importing Data into MySQL with Navicat
- Building a Sustainable Web Site
- Creating An Online Photo Album with PHP and ...
- Creating An Online Photo Album with PHP and ...
- PhpED 3.2 – More Features Than You Can Poke ...
- Creating An Online Photo Album with PHP and ...
- Creating An Online Photo Album with PHP and ...
- Security and Sessions in PHP
- Setup Your Personal Reminder System Using PHP
- Create a IP-Country Database Using PERL and ...
- Developing a Dynamic Document Search in PHP ...

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 
Support 

Developer Shed Affiliates

 




© 2003-2017 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials