Database Design Best Practices
The fundamental rule of database normalization is to never repeat the same string data in a column more than once.
A Track List That Grows
Imagine designing a music database. When several tracks belong to the same artist, it may seem natural to write the artist's name directly in every track row. The information is visible exactly where you need it. However, repeating the same string across rows creates redundancy and violates the fundamental rule of database normalization.
What do you think happens?
If many tracks belong to The Beatles, what should the Track table store for each track?
Reveal answer
One numeric ID that refers to The Beatles
The artist information belongs in a separate Artist table, while each Track row stores the artist's numeric ID. This avoids repeating the same string.
Replacing Repeated Names
Normalization begins by looking for string data that repeats across rows. Instead of storing that repeated data in the original table, create a separate table for it. In the music example, the Artist table stores each artist once. Each artist receives a unique numeric ID. The Track table then stores that numeric ID rather than the artist's full name.
Converting an Artist Name into a Reference
A Track table contains several rows for tracks by The Beatles. Design the data so the artist string is stored once.
- 1Find the repeated value
The artist name The Beatles appears in more than one track row, so it is repeated string data.
- 2Create a separate table
Create an Artist table and give The Beatles a unique numeric ID.
- 3Replace the repeated string
In each related Track row, store the artist's numeric ID instead of the full artist name.
The Beatles is stored once in the Artist table, while the Track rows reference the same numeric artist ID.
Reading a Numeric Relationship
A relationship is the connection between rows in different tables that is established by matching numeric IDs. For example, if a Track row has artist_id = 3, that row is linked to the Artist row whose ID is 3. The database can therefore identify the artist without storing the artist's name in the Track row.
To display the artist for Let It Be, follow the relationship from the Track row's artist_id to the matching Artist row. The numeric ID is not the artist's name; it is the connection that lets the database reach the row containing the name.
Benefits of the Split
Normalization makes updates simpler. If an artist changes their name, the normalized design requires one update in the Artist table. Every Track row already points to that artist through the numeric ID, so the related tracks use the updated name. In an unnormalized design, every Track row containing the old name would need to be found and changed, which creates a risk of inconsistent data.
Normalization can also save storage space. Storing The Beatles many times uses more space than storing it once and using a small numeric ID for each reference. The source describes this difference as increasingly significant as a database grows to millions of rows.
| Design | Where the artist name is stored | Update behavior | Redundancy |
|---|---|---|---|
| Repeated strings | In multiple Track rows | Each related row must be found and updated | The same string is stored repeatedly |
| Normalized design | Once in the Artist table | Update the Artist row once | Track rows store numeric references |
How normalization changes storage and maintenance
Design Mistakes to Avoid
Common mistake Writing the artist name in every Track row
What it looks likeSeveral Track rows each contain The Beatles as a string.
Why it failsThe same string data is repeated in a column, creating redundancy and violating the fundamental normalization rule.
Do this insteadStore The Beatles once in the Artist table and place its numeric ID in the related Track rows.
Common mistake Putting artist information only in the Track table
What it looks likeThe Track table contains the full artist name but there is no separate Artist table.
Why it failsUpdating an artist name requires changing every related Track row and can lead to inconsistent data.
Do this insteadCreate a dedicated Artist table with one row for each artist.
Common mistake Treating the numeric ID as the artist's name
What it looks likeA Track row has artist_id = 1, and the design assumes that the number itself contains the artist's text.
Why it failsThe numeric ID creates the relationship; the matching Artist row contains the artist name.
Do this insteadFollow the numeric ID to the Artist row when the artist name is needed.
Common mistake Failing to replace the repeated strings after creating the separate table
What it looks likeThe Artist table exists, but Track rows still store the full artist name instead of the numeric ID.
Why it failsThe repeated string data remains in the original table, so the redundancy has not been removed.
Do this insteadReplace the repeated strings in Track rows with the corresponding numeric references.
Practice the Transformation
A Track table contains rows for Let It Be and other tracks by The Beatles. Describe how you would redesign the data so the artist's string is stored only once. Then explain how a Track row would still identify its artist.
Hints
- First identify the value that repeats across Track rows.
- Create a separate table for that data and give the artist a unique numeric ID.
- Replace the repeated artist name in each Track row with that numeric ID.
- The relationship is followed by matching the Track row's numeric ID with the ID in the Artist table.
- 1Find string data that repeats across rows.
- 2Move that data into a separate table and store each distinct value once.
- 3Give each value a unique numeric ID.
- 4Store the numeric ID in the original table instead of repeating the string.
- 5Use matching numeric IDs to connect rows between the tables.
Key Takeaways
- The fundamental normalization rule is to avoid repeating the same string data in a column.
- Repeated data should be moved into a separate table and stored once.
- Rows in the original table can store numeric IDs instead of repeated strings.
- Matching numeric IDs establish relationships between rows in different tables.
- Normalization simplifies updates, reduces storage repetition, and helps prevent inconsistent data.