Normalizing Data: First, Second, and Third Normal Forms
A multi-table relational schema organizes data across separate tables to eliminate redundancy and improve data integrity. The Artist, Album, and Track tables in this example demonstrate how to structure a music library database.
From Flat Rows to Related Tables
A CSV file can place all information about a track on one line: the track title, artist name, album title, track length, rating, and play count. That arrangement is convenient for reading, but it repeats the same artist and album information whenever several tracks belong to the same artist or album. A normalized relational design separates this information into Artist, Album, and Track tables, then connects the tables with references.
Normalization organizes data to minimize redundancy and improve data integrity. In this music-library design, each table stores a specific type of entity, and foreign keys connect the entities.
The Three-Table Structure
The Artist table holds unique artist names. The Album table stores album titles and the artist_id for the artist who created each album. The Track table stores individual songs and includes information such as duration, rating, and play count, along with an album_id identifying the album to which each track belongs.
The diagram shows a chain of references rather than repeated text. An album record stores an artist_id instead of storing the artist's name directly. A track record stores an album_id instead of repeating the album information. To find the artist of a track, follow the track's album_id to the Album table, then follow that album's artist_id to the Artist table.
Tracing One CSV Row
Loading a Track from the CSV
A CSV row contains a track title, artist name, album title, track length, rating, and play count. How does the application place this information into the normalized tables?
Read the artist: The application checks whether the artist already exists in the Artist table. If the artist is absent, it inserts the artist and retrieves the new id.
Read the album: The application checks whether the album exists, using the artist_id obtained from the Artist table. If needed, it inserts the album and obtains its id.
Read the track: The application inserts the track's title and metadata into the Track table, together with the album_id that identifies its album.
Preserve relationships: The track does not need to repeat the artist name or the full album record. The references allow those related values to be retrieved later.
One flat CSV row becomes related records in Artist, Album, and Track, with identifiers connecting the records.
The important transformation is not merely splitting one large table into three smaller tables. The application must preserve the connections. It first obtains the artist's id, uses that id while handling the album, and then uses the album's id while inserting the track.
Normalization Across Three Forms
The topic is described through First, Second, and Third Normal Forms, while the concrete design in this material is the three-table music-library schema. The practical idea is to move from flat, repeated data toward a structure in which each piece of information is stored once and related records use foreign keys. Artist information belongs in Artist, album information belongs in Album, and track information belongs in Track.
The result is less repetition and simpler updates. If an artist releases a new album, the application adds one album record and multiple track records without duplicating the artist record.
Uniqueness and Safe Inserts
The UNIQUE keyword adds a database-level rule to a column. In this schema, the Artist name column is marked TEXT UNIQUE, and the title column is also marked UNIQUE in both Album and Track. These constraints prevent duplicate values in those columns.
When the application uses INSERT IGNORE, a duplicate detected by a UNIQUE constraint is silently skipped instead of raising an error. This lets the loader encounter repeated artist, album, or track data without creating another matching row.
| Column design | What the database permits | Effect during duplicate insertion |
|---|---|---|
| Column with UNIQUE | Duplicate values are prevented | The database rejects the duplicate; with INSERT IGNORE, it silently skips it |
| Column without the stated UNIQUE constraint | The source material does not identify a duplicate-prevention rule for that column | The UNIQUE behavior described here does not apply |
Following Foreign Keys
A foreign key is a reference to a row in another table. The Album table's artist_id points to the related Artist record. The Track table's album_id points to the related Album record. These references allow the database or an application to retrieve related information without copying the related text into every row.
Finding a Track's Artist
A Track record contains an album_id, but it does not directly contain the artist's name. How can the artist be found?
Start with Track: Read the track's album_id.
Follow album_id: Use that value to find the corresponding row in Album.
Read artist_id: From the Album row, read the artist_id.
Follow artist_id: Use that value to find the corresponding row in Artist and retrieve the artist name.
The relationship chain is Track to Album to Artist.
Common Design Mistakes
Copying the artist name directly into every album and track record
It repeats information that the normalized design stores in the Artist table and weakens the purpose of the foreign-key structure.
Fix:
Store the artist once in Artist and use the references between Artist, Album, and Track.Treating a CSV row as the final relational design
The CSV is flat and denormalized, while the target design separates the three entity types.
Fix:
Parse the CSV row, check Artist, check Album using artist_id, and then insert Track using album_id.Leaving out UNIQUE constraints when duplicate prevention is required
The database no longer has the duplicate-prevention rule described for those columns.
Fix:
Apply UNIQUE to the Artist name column and to the title columns in Album and Track.Using an album title without the album reference
The record cannot use the stated foreign-key chain to connect the track to its Album record.
Fix:
Store album_id in Track and use it as the reference to Album.
Practice the Mapping
A CSV row contains a track title, artist name, album title, track length, rating, and play count. Describe the order in which the application should check or insert the Artist, Album, and Track records. Identify which identifier connects Album to Artist and which identifier connects Track to Album.
Hints
- Begin with the artist name and obtain the artist id.
- Use artist_id while checking or inserting the album.
- Use album_id while inserting the track.
Explain what happens when the loader encounters an artist name that already exists and the relevant column has a UNIQUE constraint. Then explain how INSERT IGNORE changes the handling of that duplicate.
Hints
- The UNIQUE constraint prevents another matching row.
- The source material describes INSERT IGNORE as silently skipping the duplicate instead of raising an error.
Key Takeaways
- Normalization separates Artist, Album, and Track information so each piece of information can be stored once.
- Album uses artist_id to reference Artist, and Track uses album_id to reference Album.
- A track's artist can be found by following the relationship chain Track to Album to Artist.
- UNIQUE prevents duplicate artist names, album titles, and track titles in the stated schema.
- CSV data is loaded in stages: check or insert the artist, check or insert the album using artist_id, then insert the track using album_id.
Key Takeaways
- A normalized music-library schema divides flat CSV information among Artist, Album, and Track tables.
- Foreign keys preserve relationships without repeating artist and album text in every track record.
- UNIQUE constraints prevent duplicate values and work with INSERT IGNORE to handle repeated input gracefully.
- The CSV-loading process follows the dependency order Artist, then Album, then Track.
- The design reduces redundancy, supports updates, and improves data integrity.