Home arrow MySQL arrow Page 2 - 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 - Zero Form
(Page 2 of 6 )

Let's say we want to create a table of user information, and we want to store each users' Name, Company, Company Address, and some personal bookmarks, or urls.

You might start by defining a table structure like this:

users
namecompanycompany_addressurl1url2
JoeABC1 Work Laneabc.comxyz.com
JillXYZ1 Job Streetabc.comxyz.com

We would say this table is in Zero Form because none of our rules of normalization have been applied yet.

Notice the url1 and url2 fields -- what do we do when our application needs to ask for a third url? Do you want to keep adding columns to your table and hard-coding that form input field into your PHP code? Obviously not, you would want to create a functional system that could grow with new development requirements.

Let's look at the rules for the First Normal Form, and then apply them to this table.

First Normal Form

  1. Eliminate repeating groups in individual tables.
  2. Create a separate table for each set of related data.
  3. Identify each set of related data with a primary key.

Notice how we're breaking that first rule by repeating the url1 and url2 fields?
And what about Rule Three, primary keys?
Rule Three basically means we want to put some form of unique, auto-incrementing integer value into every one of our records. Otherwise, what would happen if we had two users named Joe and we wanted to tell them apart?

When we apply the rules of the First Normal Form we come up with the following table:

users
userIdnamecompanycompany_addressurl
1JoeABC1 Work Laneabc.com
1JoeABC1 Work Lanexyz.com
2JillXYZ1 Job Streetabc.com
2JillXYZ1 Job Streetxyz.com

Now our table is said to be in the First Normal Form. We've solved the problem of url field limitation, but look at the headache we've now caused ourselves.

Every time we input a new record into the users table, we've got to duplicate all that company and user name data.

Not only will our database grow much larger than we'd ever want it to, but we could easily begin corrupting our data by misspelling some of that redundant information.

Let's apply the rules of Second Normal Form.
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