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 |
| name | company | company_address | url1 | url2 |
| Joe | ABC | 1 Work Lane | abc.com | xyz.com |
| Jill | XYZ | 1 Job Street | abc.com | xyz.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 - Eliminate repeating groups in individual tables.
- Create a separate table for each set of related data.
- 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 |
| userId | name | company | company_address | url |
| 1 | Joe | ABC | 1 Work Lane | abc.com |
| 1 | Joe | ABC | 1 Work Lane | xyz.com |
| 2 | Jill | XYZ | 1 Job Street | abc.com |
| 2 | Jill | XYZ | 1 Job Street | xyz.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.
Next: Second Form >>
More MySQL Articles
More By Barry Wise
