Home arrow MySQL arrow Page 4 - 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 - Data Relationships
(Page 4 of 6 )

Before we define the Fourth Normal Form, let's look at the three basic data relationships: one-to-one, one-to-many, and many-to-many.

Look at the users table in the First Normal Form example above. For a moment let's imagine we put the url fields in a separate table, and every time we input one record into the users table we would input one row into the urls table. We would then have a one-to-one relationship: each row in the users table would have exactly one corresponding row in the urls table.

For the purposes of our application this would neither be useful nor normalized.
Now look at the tables in the Second Normal Form example. Our tables allow one user to have many urls associated with his user record.

This is a one-to-many relationship, the most common type, and until we reached the dilemma presented in the Third Normal Form, the only kind we needed.
The many-to-many relationship, however, is slightly more complex. Notice in our Third Normal Form example we have one user related to many urls.

As mentioned, we want to change that structure to allow many users to be related to many urls, and thus we want a many-to-many relationship. Let's take a look at what that would do to our table structure before we discuss it:

users
userIdnamerelCompId
1Joe1
2Jill2

companies
compIdcompanycompany_address
1ABC1 Work Lane
2XYZ1 Job Street

urls
urlIdurl
1abc.com
2xyz.com

url_relations
relationIdrelatedUrlIdrelatedUserId
111
212
321
422

In order to decrease the duplication of data (and in the process bring ourselves to the Fourth Form of Normalization), we've created a table full of nothing but primary and foreign keys in url_relations.

We've been able to remove the duplicate entries in the urls table by creating the url_relations table.

We can now accurately express the relationship that both Joe and Jill are related to each one of, and both of, the urls. So let's see exactly what the Fourth Form Of Normalization entails.
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