Designing Relational Database Schemas

Extending the same relationship chain to Track allows a query to reconstruct track information together with its album and artist context.

Programming

Interactive lab

Try it: Normalising into Lookup Tables

How normalisation removes repeated text: each distinct artist or album is stored once in a lookup table with an integer id (INSERT OR IGNORE, then SELECT id), the other table stores only that id as a foreign key, and a JOIN rebuilds the original rows. A roster preset links users and courses through a junction table.

How it works

For each flat row, INSERT OR IGNORE the repeated text into its lookup table; the UNIQUE column makes a second copy be ignored.
SELECT the text's id back — new or existing.
Store that integer id (a foreign key) in the next table instead of the text.
Many-to-many: a junction table stores (user_id, course_id) pairs plus the role; INSERT OR REPLACE keeps one row per pair.
A JOIN that follows the ids rebuilds the same rows, while each distinct string is now stored once.

Default run (27 steps): A flat table: 6 rows × 3 text columns = 18 stored strings, many of them repeated. Split it into Artist, Album and Track. … Done: 18 stored strings became 12 (179 → 130 characters). The JOIN rebuilt 6 rows, the same rows as the flat table.

Simplified: A toy relational engine in the browser, not SQLite itself: a small structured query model built from the controls (no SQL text is parsed or executed), typed INTEGER/TEXT/NULL values, and tables of at most a few rows. Results were checked against Python's sqlite3 (SQLite 3.49). Follows the Python for Everybody tracks and roster programs on at most 8 rows; the stored-string count ignores the (small) space integers take.

Educational simulation

Loading the simulation…

One Record, Three Tables

A complete track record may require information from three separate tables. Artist stores the artist row, Album stores the album row and the artist_id that links it to an Artist, and Track stores the track row and the album_id that links it to an Album. The database keeps these records separate, but their keys allow the related information to be connected when needed.

Key point

The central workflow is: insert or find the Artist, retrieve artist_id, insert or find the Album using artist_id, retrieve album_id, and then insert the Track using album_id.

What happens to a row and its primary key when an insert is new or when the row already exists?

Following the Key Chain

The INSERT OR IGNORE and SELECT pattern is a two-step sequence. First, the program attempts to insert a row. The IGNORE behavior means that an existing row is not inserted again. Immediately afterward, the program selects the primary key for the row. This gives the program the key whether the row was newly inserted or was already present.

Worked example

Passing Keys Between Inserts

An input record contains an artist, an album, and a track. Show how the identifiers connect the three inserts.

1
Artist
Insert or ignore the Artist row, then select its primary key. Store that value as artist_id.
2
Album
Insert the Album row with artist_id as its foreign key, then select the Album primary key and store it as album_id.
3
Track
Insert the Track row with album_id, linking the Track to its Album and indirectly to its Artist.

Result

The retrieved artist_id and album_id carry the relationship from one insert to the next.

sql

INSERT OR IGNORE INTO Artist (name) VALUES (...);
SELECT id FROM Artist WHERE name = ...;

INSERT OR IGNORE INTO Album (title, artist_id) VALUES (..., artist_id);
SELECT id FROM Album WHERE title = ... AND artist_id = ...;

INSERT OR REPLACE INTO Track (title, album_id, rating, count, length)
VALUES (..., album_id, ..., ..., ...);

INSERT OR IGNORE INTO Artist (name) VALUES (...);
SELECT id FROM Artist WHERE name = ...;

INSERT OR IGNORE INTO Album (title, artist_id) VALUES (..., artist_id);
SELECT id FROM Album WHERE title = ... AND artist_id = ...;

INSERT OR REPLACE INTO Track (title, album_id, rating, count, length)
VALUES (..., album_id, ..., ..., ...);

Respecting Table Dependencies

The population order is determined by foreign keys. Artist comes first because Album stores artist_id. Album comes second because Track stores album_id. Track comes last because its foreign key depends on an Album row that already exists.

Which table must be populated first, and how does each parent key enable the next dependent insert?

Edge case

If an Album is inserted before its Artist exists, the database rejects the insert with a foreign key constraint error. The same problem occurs if a Track is inserted before its Album exists. The dependency order is therefore a requirement, not merely a convenient convention.

Best practice

Use the retrieved key immediately in the next dependent insert. This keeps the relationship tied to the actual primary key returned for the parent row rather than copying an artist name or other descriptive data into the dependent table.

Reading Relationships Across Tables

A foreign key is a primary-key value from one table stored in another table. In this design, Album.artist_id stores the primary key of the related Artist row, while Track.album_id stores the primary key of the related Album row. The values establish relationships without copying the artist's name or other artist data into every Album row.

What does each table contain, and how do matching key columns connect the rows?

When a complete record is needed for reading, a JOIN matches rows using these primary-key and foreign-key values. A JOIN between Album and Artist on Album.artist_id = Artist.id can produce a result containing both the album title and the artist name. Extending the same relationship chain to Track allows a query to reconstruct track information together with its album and artist context.

sql

SELECT Track.title, Album.title, Artist.name
FROM Track
JOIN Album ON Track.album_id = Album.id
JOIN Artist ON Album.artist_id = Artist.id;

SELECT Track.title, Album.title, Artist.name
FROM Track
JOIN Album ON Track.album_id = Album.id
JOIN Artist ON Album.artist_id = Artist.id;

Stored Data and Joined Results

The normalized design stores related information separately. An Album row stores its own album data and artist_id; it does not store a copied artist name. A Track row stores its own track data and album_id. A joined query creates a combined result for reading by matching those key values.

Location	Stored or produced information	Connection
Artist table	Artist row and its primary key	Provides the key used by Album.artist_id
Album table	Album row and artist_id	Links the Album to Artist
Track table	Track row and album_id	Links the Track to Album
JOIN result	Track, Album, and Artist columns together	Matches primary and foreign key values

Separate storage supports relationships; JOIN produces a combined view for reading.

Mistakes in the Insertion Chain

Common mistake
Inserting an Album before its Artist
What it looks like
Attempting to create an Album when the required Artist row does not yet exist.
Why it fails
The Album needs artist_id, and the database cannot satisfy that foreign-key relationship if the Artist has not been inserted.
Do this instead
Insert or ignore the Artist first, select its primary key, and use that value in the Album insert.
Common mistake
Inserting a Track before its Album
What it looks like
Attempting to create a Track without a previously retrieved album_id.
Why it fails
Track uses album_id to link to Album, so the parent Album must already exist.
Do this instead
Complete the Album insert and SELECT step before inserting the Track.
Common mistake
Stopping after INSERT OR IGNORE
What it looks like
Inserting the Artist and then trying to create the Album without selecting the Artist key.
Why it fails
The next insert needs the concrete artist_id value.
Do this instead
Immediately select and store the primary key after the insert attempt.
Common mistake
Copying an artist name into the Album relationship field
What it looks like
Treating a descriptive name as if it were the relationship value.
Why it fails
The relationship is maintained by matching the Artist primary key with Album.artist_id.
Do this instead
Store the Artist primary key as artist_id.
Common mistake
Using the wrong insertion behavior for repeated Track data
What it looks like
Treating a repeated Track exactly like a repeated Artist or Album.
Why it fails
The described insertion flow uses INSERT OR REPLACE for Track so repeated track data can update metadata such as rating, count, and length.
Do this instead
Use the Track behavior appropriate to the flow: INSERT OR REPLACE when the same track may appear again and its metadata may need updating.

Practice the Dependency Order

MEDIUM

A CSV line provides an artist name, an album title, a track title, and track metadata. Write the order of database actions needed to store the record. Include where each SELECT occurs and identify the foreign key passed to the next insert.

Hints

Begin with the table that does not depend on either of the other two tables.
After the Artist insert, retrieve artist_id.
After the Album insert, retrieve album_id.
The Track row receives album_id.

Your attempt (not saved)

What do you think happens?

Which action must happen first when storing a new Artist, Album, and Track record?

Insert the Track
Insert the Album
Insert or ignore the Artist

Reveal answer

Answer:

Insert or ignore the Artist

Album needs artist_id, and Track needs album_id. The Artist must therefore be inserted or found before the Album, and the Album must be inserted or found before the Track.

Schema Workflow Summary

1
INSERT OR IGNORE followed by SELECT retrieves the primary key needed for the next related insert.
2
Populate the tables in dependency order: Artist, then Album, then Track.
3
Album.artist_id links an Album to an Artist, and Track.album_id links a Track to an Album.
4
JOIN clauses match primary-key and foreign-key values to reconstruct complete records for reading.
5
Track uses INSERT OR REPLACE in the described flow when repeated track data may update rating, count, or length.

At a glance

Use INSERT OR IGNORE and then SELECT to obtain a row's primary key.
Insert parent rows before dependent rows: Artist before Album, and Album before Track.
Pass artist_id into Album and album_id into Track to maintain foreign-key relationships.
Use JOIN clauses to combine separately stored Artist, Album, and Track data into a complete result.

Related concepts

SQL JOIN clauses

More concepts starting with D