Third Normal Form
- Eliminate fields that do not depend on the key.
Our Company Name and Address have nothing to do with the User Id, so they should have their own Company Id:
| 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 | relUserId | url |
| 1 | 1 | abc.com |
| 2 | 1 | xyz.com |
| 3 | 2 | abc.com |
| 4 | 2 | xyz.com |
Now we’ve got the primary key compId in the companies table related to the foreign key in the users table
called relCompId, and we can add 200 users while still only inserting the name “ABC” once. Our users and urls tables can grow
as large as they want without unnecessary duplication or corruption of data. Most developers will say
the Third Normal Form is far enough, and our data schema could easily handle the load of an entire enterprise, and
in most cases they would be correct.
called relCompId, and we can add 200 users while still only inserting the name “ABC” once. Our users and urls tables can grow
as large as they want without unnecessary duplication or corruption of data. Most developers will say
the Third Normal Form is far enough, and our data schema could easily handle the load of an entire enterprise, and
in most cases they would be correct.
But look at our url fields – do you notice the duplication of data? This is prefectly acceptable if we are not
pre-defining these fields. If the HTML input page which our users are filling out to input this data allows a free-form
text input there’s nothing we can do about this, and it’s just a coincedence that Joe and Jill both input the same
bookmarks. But what if it’s a drop-down menu which we know only allows those two urls, or maybe 20 or even more.
We can take our database schema to the next level, the Fourth Form, one which many developers overlook
because it depends on a very specific type of relationship, the many-to-many relationship, which we have not yet
encountered in our application.
pre-defining these fields. If the HTML input page which our users are filling out to input this data allows a free-form
text input there’s nothing we can do about this, and it’s just a coincedence that Joe and Jill both input the same
bookmarks. But what if it’s a drop-down menu which we know only allows those two urls, or maybe 20 or even more.
We can take our database schema to the next level, the Fourth Form, one which many developers overlook
because it depends on a very specific type of relationship, the many-to-many relationship, which we have not yet
encountered in our application.
