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
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.
Find the repeated value: The artist name The Beatles appears in more than one track row, so it is repeated string data.
Create a separate table: Create an Artist table and give The Beatles a unique numeric ID.
Replace 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 |
Design Mistakes to Avoid
Writing the artist name in every Track row
The same string data is repeated in a column, creating redundancy and violating the fundamental normalization rule.
Fix:
Store The Beatles once in the Artist table and place its numeric ID in the related Track rows.Putting artist information only in the Track table
Updating an artist name requires changing every related Track row and can lead to inconsistent data.
Fix:
Create a dedicated Artist table with one row for each artist.Treating the numeric ID as the artist's name
The numeric ID creates the relationship; the matching Artist row contains the artist name.
Fix:
Follow the numeric ID to the Artist row when the artist name is needed.Failing to replace the repeated strings after creating the separate table
The repeated string data remains in the original table, so the redundancy has not been removed.
Fix:
Replace 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.
- Find string data that repeats across rows.
- Move that data into a separate table and store each distinct value once.
- Give each value a unique numeric ID.
- Store the numeric ID in the original table instead of repeating the string.
- Use 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.