Third Normal Form: Removing Transitive Dependencies
Redundancy occurs when the same information is stored multiple times across different rows in a table, such as repeating an artist's name in every track row.
The Attractive Shortcut
Suppose a Track table already stores a song title and play count. You now want to add artist information. The most natural approach is to add artist details directly to the Track table and fill them in for every row. With a few tracks, this design looks clean and reasonable. The hidden problem appears when the same artist information is repeated across many rows.
Tracing the Repeated Values
Redundancy occurs when the same information is stored multiple times across different rows. For example, if an artist has many tracks, the artist name and other artist details may appear once in every track row. The track rows are different, but the artist information is the same. The more tracks an artist has, the more copies of that information the table contains.
| Track title | Play count | Artist name | Eye color |
|---|---|---|---|
| Track A | 1200 | Frank Sinatra | Blue |
| Track B | 950 | Frank Sinatra | Blue |
| Track C | 700 | Frank Sinatra | Blue |
Illustrative rows showing the same artist details repeated for multiple tracks.
Why Transitive Dependencies Matter
The track and the artist are related, but they are not the same kind of information. A track row identifies a track and can use an artist reference to identify its artist. Artist attributes, such as an artist name or eye color, describe the artist. If those attributes are copied into every track row, the track table stores artist information indirectly and repeatedly rather than storing it in one dedicated place. This indirect dependency is the design problem addressed by Third Normal Form.
Redundancy is the storage of the same information multiple times across different rows. In a normalized design, each piece of information should be stored in exactly one place, while references such as foreign keys connect related data in separate tables.
The Update Anomaly
Changing an Artist Attribute
Frank Sinatra has 1200 songs in the redundant table. The eye-color value should change from Blue to Light Blue. What must be updated?
- 1Locate the copies
The artist details appear in every one of the 1200 track rows.
- 2Apply the change
The update must touch all 1200 rows if every copy is to remain consistent.
- 3Consider a partial update
If the system crashes or application logic updates only some rows, the table can contain both Blue and Light Blue for the same artist.
- 4Evaluate the result
The database is now inconsistent, and the rows do not reveal which version is correct.
One fact required many updates, creating an update anomaly when the copies do not all change together.
This risk grows with the size of the database. A repeated attribute across millions or billions of rows creates a large maintenance task. Normalization prevents the problem by keeping the attribute in one place, so changing it does not require changing every related track row.
The Deletion Anomaly
Redundancy also couples two different facts: the existence of a track and the existence of an artist. Suppose an artist has only one track in the table. If that track is deleted because it is no longer available, the row containing the artist details disappears too. Deleting a track has accidentally deleted the only stored record of the artist.
Separating the Tables
The normalized solution is to create a separate Artist table. Store each artist's information once in that table. The Track table keeps track-specific information and uses a foreign key to reference the related artist. The relationship remains available, but the artist name and other artist attributes are no longer copied into every track row.
| Redundant design | Separated design |
|---|---|
| Artist details are repeated in track rows | Artist details are stored once in Artist |
| An artist update may require many row updates | The artist information has one stored copy to maintain |
| Deleting the last track can delete artist information | Track information and artist information are stored separately |
| The relationship is represented by repeated values | The relationship is represented through a foreign-key reference |
The design change that removes redundancy and its maintenance consequences.
Recognizing the Pattern
Common mistake Assuming a small test table proves the design is safe
What it looks likeA few track rows show the same artist name, and every query returns a reasonable result.
Why it failsRedundancy becomes more difficult to maintain as the number of related rows grows.
Do this insteadAsk whether the same fact is being stored repeatedly and whether it belongs in a separate table.
Common mistake Updating only one repeated copy of an artist attribute
What it looks likeOne track row changes an eye-color value from Blue to Light Blue while other rows keep Blue.
Why it failsThe table becomes inconsistent, and it is no longer clear which value is correct.
Do this insteadStore the artist attribute once and reference the artist from Track rows.
Common mistake Treating a track deletion as unrelated to artist data without checking the design
What it looks likeDeleting an artist's only track also removes the only row containing that artist's details.
Why it failsThe artist information was stored only as part of the track row.
Do this insteadSeparate artist information from track information.
Common mistake Confusing a relationship with a repeated copy of descriptive data
What it looks likeRepeating an artist name in every Track row is treated as the only way to connect tracks to an artist.
Why it failsA foreign-key reference can connect the tables without copying the artist details into every track row.
Do this insteadKeep the relationship through a reference and store each fact in one appropriate place.
A course table stores a student's name in every enrollment row. A student takes five courses. If the student's name changes, what maintenance problem can occur? If the enrollment in the student's only course is deleted, what information might be lost?
Hints
- Look for the value that is repeated across enrollment rows.
- Separate the question about changing a value from the question about deleting a row.
- Use the Artist and Track pattern as a comparison.
A Reliable Design Test
- 1Find values that repeat across multiple rows.
- 2Ask which real-world object each repeated value describes.
- 3Check whether changing that value would require updating many rows.
- 4Check whether deleting one row could remove the only copy of another fact.
- 5Move the repeated object's attributes into a separate table.
- 6Use a foreign-key reference to preserve the relationship between the tables.
What do you think happens?
An instructor teaches 20 courses, and the instructor's name and email are stored in every course row. If the email changes, what is the likely result in this design?
Reveal answer
Twenty repeated values may need to change
The source example identifies repeated instructor information across 20 course rows. A change to the instructor's email therefore creates the same maintenance risk as repeated artist information.
Key Takeaways
- 1Redundancy means storing the same information repeatedly across rows.
- 2Repeated artist attributes create update anomalies when only some copies change.
- 3Deleting the last related row can cause a deletion anomaly by removing the only copy of another fact.
- 4Third Normal Form addresses this pattern by separating artist data from track data.
- 5Foreign-key references preserve relationships without repeating the related entity's descriptive data.
At a glance
- Redundancy occurs when one fact, such as an artist's name or eye color, is copied into many track rows.
- An update anomaly occurs when repeated copies are not all changed consistently.
- A deletion anomaly occurs when deleting a track also removes the only stored copy of an artist's details.
- Third Normal Form reduces these problems by storing artist attributes once in an Artist table and connecting tracks with a foreign key.