A table in 3rd normal form follows all the rules of 2nd normal form, and one additional rule:
Notice that this is only slightly different from second normal form. In second normal form we can't have a column completely dependent on only part of the primary key, and in third normal form we can't have a column that is entirely dependent on anything that isn't the primary key.
In this table, the primary key is simply the id column.
| id | name | first_initial | |
|---|---|---|---|
| 1 | Lane | l | [email protected] |
| 2 | Breanna | b | [email protected] |
| 3 | Lane | l | [email protected] |
This table is in 2nd normal form because first_initial is not dependent on a part of the primary key. However, because it is dependent on the name column, it doesn't adhere to 3rd normal form.
The way to convert the table above to 3NF is to add a new table that maps a name directly to its first_initial. Notice how similar this solution is to 2NF.
| id | name | |
|---|---|---|
| 1 | Lane | [email protected] |
| 2 | Breanna | [email protected] |
| 3 | Lane | [email protected] |
| name | first_initial |
|---|---|
| Lane | l |
| Breanna | b |
The same exact rule of thumb applies to the second and third normal forms.
Optimize for data integrity and data de-duplication first by adhering to 3NF. If you have speed issues, de-normalize accordingly.
This rollout of business accounts is really causing some headaches for our development team. The companies table has been a disaster. Our database architect pointed out that the idea behind the size field is redundant. If a company has more than 100 employees, we consider it "large", otherwise we consider it "small". That's something we can calculate from the num_employees field, rather than storing it separately.
size column from the companies table by modifying the CREATE TABLE statement.SELECT statement to calculate a size field in the result set that works the same way. Return the other fields as normal.Remember the IIF function and the AS clause.