Writing JOIN Queries to Retrieve Data Across Multiple Tables

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.

Programming

From One Flat Record to Three Tables

A music-library CSV record can contain a track title, artist name, album title, track length, rating, and play count all on one line. A normalized database does not keep every value together in one repeated row. Instead, it separates the information into Artist, Album, and Track tables, then connects those tables with references. The result reduces repeated data and improves data integrity.

How does one flat track record become related rows in Artist, Album, and Track?

The Three-Table Schema

Each table represents a different kind of entity. Artist stores unique artist names. Album stores album titles and the artist_id that identifies the artist who created each album. Track stores individual songs and metadata such as duration, rating, and play count. A track does not store the artist name directly. It stores album_id, which points to its album.

How are the three tables connected, and what does each table contain?

Definition

A foreign key is a value in one table that references the id value of a row in another table. In this schema, Album.artist_id references Artist, and Track.album_id references Album.

Key point

The relationships form a chain: Track to Album through album_id, then Album to Artist through artist_id. To find a track's artist, follow both references.

Following Keys Through a JOIN

A JOIN retrieves related information by matching the key values that connect rows. Begin with a Track row. Its album_id identifies the related Album row. The Album row then supplies artist_id, which identifies the related Artist row. The resulting retrieval can present track, album, and artist information together even though the database stores those facts in separate tables.

Worked example

Tracing One Track to Its Artist

Suppose a Track row contains an album_id that identifies one Album row. That Album row contains an artist_id that identifies one Artist row. Trace the retrieval path.

1
Start with Track
Read the track's album_id value. This value is the link to the related Album row.
2
Reach Album
Use album_id to retrieve the album title and the album's artist_id.
3
Reach Artist
Use artist_id from the Album row to retrieve the artist name.
4
Combine the result
The related track, album, and artist values can now be returned together, although they remain stored in their respective tables.

Result

The JOIN follows the chain Track.album_id to Album, then Album.artist_id to Artist.

How does a JOIN use matching key values to combine an Artist, Album, and Track row into one result?

Loading CSV Data Safely

The tracks_csv.py application reads a CSV file exported from Dr. Chuck's iTunes library. Each line contains the track title, artist name, album title, track length, rating, and play count. Because this is flat, denormalized input, the application must distribute the values into the normalized tables.

1
Check whether the artist already exists in the Artist table.
2
If the artist is absent, insert it and retrieve its new id.
3
Check whether the album exists using the artist_id that was obtained.
4
If the album is absent, insert it and retrieve its album id.
5
Insert the track and store its album_id as the link to the Album table.

What changes when repeated artist and album data is transformed from one flat CSV record into separate related tables?

Normalization and Data Integrity

Definition

Normalization is the process of organizing data to minimize redundancy and improve data integrity. In this schema, each piece of information is stored once when possible, and foreign keys connect the related records.

If Queen releases a new album, the normalized design adds one row to Album and multiple rows to Track. It does not create a new copy of the artist record for every track. This reduces repetition and makes updates simpler because the artist information is maintained in one place.

Key point

Normalization separates artist, album, and track facts while foreign keys preserve the relationships between them. A JOIN temporarily brings related values together for retrieval without undoing that organization.

UNIQUE Constraints

The UNIQUE keyword prevents duplicate entries in the column where it is applied. In this schema, Artist.name is marked TEXT UNIQUE. The same constraint is applied to the title column in Album and Track. If an insert attempts to create a duplicate value in one of those constrained columns, the database rejects that duplicate.

Table	Constrained column	Purpose
Artist	name	Prevents duplicate artist names
Album	title	Prevents duplicate album titles
Track	title	Prevents duplicate track titles

The source schema applies UNIQUE to the named columns.

Using UNIQUE is more concise than creating separate indexes manually. It both prevents duplicates and enables efficient lookups through one keyword in the table definition.

Mistakes with Relationships

Common mistake
Expecting Album.artist_id to contain the artist's name
What it looks like
Treating artist_id as though it were a text field containing the artist name
Why it fails
The Album table stores the id value from Artist, not the artist name itself.
Do this instead
Use the artist_id reference to reach the related Artist row and retrieve its name.
Common mistake
Looking for an artist directly in Track
What it looks like
Trying to obtain the artist without following Track.album_id
Why it fails
The Track table links to Album, and Album links to Artist.
Do this instead
Follow the chain from Track to Album through album_id, then from Album to Artist through artist_id.
Common mistake
Treating repeated CSV rows as separate artist records
What it looks like
Inserting the same artist once for every track
Why it fails
That creates redundancy and can violate the UNIQUE constraint on Artist.name.
Do this instead
Check whether the artist exists, reuse its id when it does, and insert it only when it is absent.
Common mistake
Ignoring duplicate handling during CSV loading
What it looks like
Inserting repeated artist, album, or track values without considering UNIQUE constraints
Why it fails
The database can reject duplicate values when the relevant column is UNIQUE.
Do this instead
Use the uniqueness constraints together with INSERT IGNORE so duplicate inserts are skipped gracefully.

Practice the Retrieval Path

EASY

A track row contains album_id. The related album row contains artist_id. Explain, in order, which tables you follow to retrieve the track title, album title, and artist name together.

Hints

Begin with the Track table.
Use album_id to reach Album.
Use artist_id from Album to reach Artist.

Your attempt (not saved)

MEDIUM

A CSV file contains several tracks by the same artist. Describe how the loader should avoid creating repeated artist records while still inserting the tracks.