Designing Relational Databases: Multiple Tables and Normalization
The fundamental rule of database normalization is to never repeat the same string data in a column more than once.
Why Repeated Data Becomes a Problem
When a database is first designed, repeating information can seem convenient. In a music database, for example, you might write the artist name in every track row. The name is immediately visible, so the design appears simple. However, repeating the same string across rows creates redundancy and violates the fundamental rule of normalization.
The fundamental rule of database normalization is to never repeat the same string data in a column more than once.
The goal is not merely to make a table look tidy. The goal is to store each repeated piece of string data once, then represent later occurrences with a numeric key. This changes the design from one large, repetitive table into multiple tables connected by numeric IDs.
Spotting Redundancy in a Track Table
| track | artist |
|---|---|
| Let It Be | The Beatles |
| Yesterday | The Beatles |
| Hey Jude | The Beatles |
The artist string appears repeatedly in the artist column.
The repeated value is not a problem because it is a long name. It is a problem because the same string data is stored in the same column more than once. If the artist's name later changes, every track row containing that name would need to be found and updated. Missing one row could leave the database inconsistent.
What do you think happens?
Suppose a database stores an artist name in many track rows. What design change removes the repeated artist strings?
Reveal answer
Answer: Create a separate artist table and store a numeric artist ID in each track row
The artist information is stored once in a dedicated table. Track rows then reference that artist using a numeric ID.
Moving Repeated Information into Its Own Table
Normalization is a process. First, identify string data that repeats across rows. Next, create a separate table for that data. Finally, replace the repeated strings in the original table with numeric references. In the music example, artist information moves into an Artist table, while track information remains in the Track table.
Separating Artist Data from Track Data
Design the data so that The Beatles is stored once while several tracks can still identify that artist.
Find the repeated value: The Beatles appears in multiple track rows. It is repeated string data in the artist column.
Create the Artist table: Give The Beatles one row in a dedicated Artist table and assign it a unique numeric ID.
Change the Track table: Replace the repeated artist name in each track row with the numeric ID for The Beatles.
Keep the connection: The numeric ID lets each track row refer back to the single Artist row.
The Beatles is stored once in the Artist table, while the Track table stores the corresponding numeric artist ID for each track.
| Artist table: artist_id | Artist table: name | Track table: track | Track table: artist_id |
|---|---|---|---|
| 1 | The Beatles | Let It Be | 1 |
| 1 | The Beatles | Yesterday | 1 |
| 1 | The Beatles | Hey Jude | 1 |
The artist name is stored once conceptually in the Artist table; each Track row stores its numeric reference.
Following a Numeric Relationship
A relationship is the connection between two tables created by matching numeric IDs. If a Track row has artist_id = 3, that row is linked to the Artist row whose ID is 3. The database can therefore determine which artist belongs to the track without storing the artist's full name in the Track table.
The relationship works in both directions conceptually: a Track row can use its numeric ID to find the matching Artist row, and the matching Artist row identifies the artist associated with that track. When the artist name needs to be displayed, the database follows the numeric reference to the Artist table.
Benefits of Normalized Design
Normalization makes updates simpler. If an artist changes their name, the name can be updated once in the Artist table. All tracks that reference that artist's numeric ID then use the updated name. In an unnormalized design, every Track row containing the old name would need to be found and changed, creating a risk of missed rows and inconsistent data.
Normalization also saves storage space. Storing The Beatles many times uses more space than storing the name once and referencing it with a small numeric ID. The source describes this difference as becoming significant as a database grows to millions of rows.
Mistakes in Table Design
Repeating an artist name in every Track row
The same string data is repeated in a column, which violates the fundamental normalization rule and makes updates harder.
Fix:
Store the artist once in an Artist table and place the artist's numeric ID in each related Track row.Removing repeated text without preserving a reference
The Track rows no longer contain a way to connect to the artist information.
Fix:
Replace the repeated string with a numeric ID that matches the corresponding row in the separate table.Updating only some repeated rows
The database can contain inconsistent artist information for related tracks.
Fix:
Keep the artist name in one Artist row so the name is updated once.Treating the numeric ID as unrelated data
The numeric ID is what creates the relationship between the tables.
Fix:
Match the numeric ID in the Track row with the same ID in the Artist table.
| Repeated-string design | Normalized design |
|---|---|
| Artist name appears in multiple Track rows | Artist name is stored once in an Artist table |
| Updates require finding many rows | An artist name is updated once |
| Repeated strings use more storage | Track rows use numeric references |
| Missed updates can create inconsistency | Related tracks reference the same artist row |
Normalization Practice
A Track table contains these rows: Let It Be with The Beatles, Yesterday with The Beatles, and Imagine with John Lennon. Describe how you would redesign the data using multiple tables and numeric IDs. Identify which string values should be stored once, what the separate table should contain, and what each Track row should store instead of the artist name.
Hints
- Look for artist strings that repeat in the same column.
- Create one row in a separate table for each artist value.
- Replace each artist name in the Track rows with the matching numeric artist ID.
Checking a Proposed Design
A Track row contains Let It Be and artist_id = 1. The Artist table contains artist_id = 1 and The Beatles. What relationship does the numeric ID establish?
Read the Track reference: The Track row identifies artist_id as 1.
Find the matching Artist row: The Artist row with ID 1 contains The Beatles.
Interpret the relationship: The matching numeric IDs connect Let It Be to The Beatles without storing the artist name in the Track row.
The relationship shows that Let It Be belongs to The Beatles.
Design Checklist
- Look for string data that repeats in a column.
- Create a separate table for the repeated information.
- Give each stored item a unique numeric ID.
- Replace repeated strings in the original table with the matching numeric ID.
- Use matching numeric IDs to connect rows across the tables.
- When information changes, update the single stored value in its dedicated table.
- Normalization means avoiding repeated string data in a column. Instead of writing an artist name in every Track row, store the artist once in an Artist table and use a numeric ID in the Track table. Matching IDs establish relationships between rows in different tables. This design reduces redundancy, simplifies updates, saves storage space, and helps prevent inconsistent data.
Key Takeaways
- Normalization prevents the same string data from being repeated in a column.
- Repeated information should be moved into a separate table and stored once.
- Numeric IDs replace repeated strings and provide references between tables.
- Matching numeric IDs establish relationships between related rows.
- Normalized designs make updates simpler, save storage space, and reduce inconsistency.