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?
Locate the copies: The artist details appear in every one of the 1200 track rows.
Apply the change: The update must touch all 1200 rows if every copy is to remain consistent.
Consider 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.
Evaluate 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 |
Recognizing the Pattern
Assuming a small test table proves the design is safe
Redundancy becomes more difficult to maintain as the number of related rows grows.
Fix:
Ask whether the same fact is being stored repeatedly and whether it belongs in a separate table.Updating only one repeated copy of an artist attribute
The table becomes inconsistent, and it is no longer clear which value is correct.
Fix:
Store the artist attribute once and reference the artist from Track rows.Treating a track deletion as unrelated to artist data without checking the design
The artist information was stored only as part of the track row.
Fix:
Separate artist information from track information.Confusing a relationship with a repeated copy of descriptive data
A foreign-key reference can connect the tables without copying the artist details into every track row.
Fix:
Keep 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
- Find values that repeat across multiple rows.
- Ask which real-world object each repeated value describes.
- Check whether changing that value would require updating many rows.
- Check whether deleting one row could remove the only copy of another fact.
- Move the repeated object's attributes into a separate table.
- Use 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
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
- Redundancy means storing the same information repeatedly across rows.
- Repeated artist attributes create update anomalies when only some copies change.
- Deleting the last related row can cause a deletion anomaly by removing the only copy of another fact.
- Third Normal Form addresses this pattern by separating artist data from track data.
- Foreign-key references preserve relationships without repeating the related entity's descriptive data.
Key Takeaways
- 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.