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 |
| userId | name | relCompId |
| 1 | Joe | 1 |
| 2 | Jill | 2 |
companies |
| compId | company | company_address |
| 1 | ABC | 1 Work Lane |
| 2 | XYZ | 1 Job Street |
urls |
| urlId | url |
| 1 | abc.com |
| 2 | xyz.com |
url_relations |
| relationId | relatedUrlId | relatedUserId |
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 2 | 1 |
| 4 | 2 | 2 |
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.
Next: Fourth Normal Form >>
More MySQL Articles
More By Barry Wise
