Data Relationships
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.
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.
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:
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 foriegn keysin 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:
created a table full of nothing but primary and foriegn keysin 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:
