Designing Tables with Primary and Foreign Keys
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 Hidden Cost of a Convenient Table
When you first add artist information to a Track table, the most natural choice may be to add artist and eyes columns directly to that table. The design appears simple: each track row contains its title, play count, artist name, and the artist's eye color. With a few rows, the table can look clean and organized. The problem appears when several tracks belong to the same artist. Facts about the artist are then copied into every track row instead of being stored once.
Data redundancy occurs when the same information is stored multiple times across different rows in a table. Repeating an artist's name or eye color in every track row is an example of redundancy.
Tracing Redundancy Across Rows
Imagine that Frank Sinatra has 1200 songs in the table. In the redundant design, every one of those 1200 rows contains Frank Sinatra in the artist column and Blue in the eyes column. The table contains many track facts, but it also contains 1200 copies of the same artist facts. The repetition may not be obvious when viewing one row, but it becomes significant when the rows are considered together.
| Track information | Artist | Eye color |
|---|---|---|
| Track 1 | Frank Sinatra | Blue |
| Track 2 | Frank Sinatra | Blue |
| Track 3 | Frank Sinatra | Blue |
| Many more tracks | Frank Sinatra repeated | Blue repeated |
A redundant design repeats artist attributes in every track row.
Redundancy is not merely a matter of extra storage. It couples two different kinds of information: details about a track and details about an artist. A track row now carries the artist's information as a copy. Because the same fact exists in many rows, every operation that changes or removes a row must be considered for its effect on the repeated artist information.
The Update Anomaly
Suppose the stored eye color for Frank Sinatra must change from Blue to Light Blue. In the redundant design, the change must be applied to all 1200 track rows. If every copy is updated correctly, the rows agree. However, updating only some rows produces inconsistent data: some rows say Blue and others say Light Blue. Once that happens, the database itself does not tell you which version is correct.
What do you think happens?
If only some of the repeated artist rows are changed from Blue to Light Blue, what condition results?
Reveal answer
Answer: Some rows contain Blue and some contain Light Blue.
A partial update leaves different copies of the same fact with different values. This inconsistency is the update anomaly.
The Deletion Anomaly
Redundancy also creates a deletion anomaly. Suppose a track is removed because it is no longer available. If that track is the only track by a particular artist in the table, deleting the track row also removes the only stored record of that artist. A decision about a track has accidentally removed artist information. The design has coupled the existence of the artist to the existence of at least one track row.
Separating Artists from Tracks
Normalization prevents these anomalies by storing each piece of information in exactly one place. Instead of repeating artist details in every Track row, create a separate Artist table that stores the artist information once. The Track table then references the artist with a foreign key. A track row contains track-specific information and an artist_id reference; the matching Artist row contains the artist-specific information.
| Design | Where artist information is stored | Main consequence |
|---|---|---|
| Redundant table | Repeated in track rows | Updates can become inconsistent and deletions can lose artist information |
| Normalized design | Once in the Artist table | Track rows refer to the artist through a foreign key |
With the separated design, changing an artist attribute means changing the single artist record rather than searching for every track row that copied the value. Removing a track does not automatically remove the artist record, because the artist information is stored separately. This is why normalization is a practical reliability principle rather than merely a theoretical preference.
A Second Domain Example
Students, Courses, and Instructors
A design stores a student's name, course name, and instructor email in every enrollment row. A student takes five courses, and an instructor teaches twenty courses.
Find repeated facts: The student's name appears in five enrollment rows. The instructor's name and email appear in twenty course rows.
Predict an update anomaly: If the instructor's email changes, all twenty copies must be updated. Updating only some rows leaves old and new email values in the same database.
Predict a deletion anomaly: If a student's enrollment in their only course is deleted, the student's name may disappear from the database because that enrollment row contained the only copy.
Choose a separation: Student information and instructor information should not be repeated in every enrollment or course row. Related tables can store those facts once, while references connect the records.
Repeated student and instructor attributes produce the same update and deletion problems as repeated artist attributes.
Design Check
A table contains a student's name, course name, and instructor email in every enrollment row. The student takes five courses, and the instructor teaches twenty courses. Identify two repeated values, predict one update anomaly, predict one deletion anomaly, and describe which information should be stored separately.
Hints
- Look for facts that describe a student or instructor rather than one particular enrollment.
- For the update anomaly, consider what happens when the instructor's email changes.
- For the deletion anomaly, consider deleting the student's enrollment in their only course.
Key Takeaways
- Redundancy means storing the same information repeatedly across related rows.
- Repeated artist information makes a single change require many updates.
- A partial update creates an update anomaly in which rows disagree about the same fact.
- Deleting the last track row for an artist can remove the only stored copy of the artist's information.
- Normalization stores each piece of information once and uses foreign-key references to connect separate tables.
Key Takeaways
- Data redundancy occurs when the same fact is copied into multiple rows.
- Repeated attributes make updates difficult because every copy must remain consistent.
- An update anomaly occurs when only some redundant copies are changed.
- A deletion anomaly occurs when deleting one row also removes the only stored copy of related information.
- Normalization separates Artist and Track data, stores artist information once, and connects it through a foreign key.