Normalizing Data: First, Second, and Third Normal Forms

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.

Programming

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.

Key point

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.

What contains what, and how are artists, albums, and tracks connected across separate tables?

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

Worked example

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?

1
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.
2
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.
3
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.
4
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.

Result

One flat CSV row becomes related records in Artist, Album, and Track, with identifiers connecting the records.

How does each field from a flat CSV row move into the appropriate Artist, Album, and Track tables?

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.

What changes as repeated artist and album information is reorganized into progressively normalized tables?

Key point

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

The UNIQUE keyword provides the duplicate-prevention behavior described for Artist names and the Album and Track title columns.

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.

Worked example

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?

1
Start with Track
Read the track's album_id.
2
Follow album_id
Use that value to find the corresponding row in Album.
3
Read artist_id
From the Album row, read the artist_id.
4
Follow artist_id
Use that value to find the corresponding row in Artist and retrieve the artist name.

Result

The relationship chain is Track to Album to Artist.

Common Design Mistakes

Common mistake
Copying the artist name directly into every album and track record
What it looks like
A Track row stores the artist's full name instead of using album_id to reach the Album table and then artist_id to reach Artist.
Why it fails
It repeats information that the normalized design stores in the Artist table and weakens the purpose of the foreign-key structure.
Do this instead
Store the artist once in Artist and use the references between Artist, Album, and Track.
Common mistake
Treating a CSV row as the final relational design
What it looks like
Keeping the track title, artist name, album title, duration, rating, and play count together as repeated database records.
Why it fails
The CSV is flat and denormalized, while the target design separates the three entity types.
Do this instead
Parse the CSV row, check Artist, check Album using artist_id, and then insert Track using album_id.
Common mistake
Leaving out UNIQUE constraints when duplicate prevention is required
What it looks like
Defining artist names, album titles, or track titles without the stated UNIQUE constraint.
Why it fails
The database no longer has the duplicate-prevention rule described for those columns.
Do this instead
Apply UNIQUE to the Artist name column and to the title columns in Album and Track.
Common mistake
Using an album title without the album reference
What it looks like
A Track record contains album text but no album_id.
Why it fails
The record cannot use the stated foreign-key chain to connect the track to its Album record.
Do this instead
Store album_id in Track and use it as the reference to Album.

Practice the Mapping

MEDIUM

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.

Your attempt (not saved)

EASY

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.