Designing Relational Database Schemas

The INSERT OR IGNORE and SELECT pattern is a two-step sequence that inserts a row and immediately retrieves its primary key for use in subsequent inserts.

Programming

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.

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.

Passing Keys Between Inserts

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

Artist: Insert or ignore the Artist row, then select its primary key. Store that value as artist_id.

Album: Insert the Album row with artist_id as its foreign key, then select the Album primary key and store it as album_id.

Track: Insert the Track row with album_id, linking the Track to its Album and indirectly to its Artist.

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?

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.

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

Inserting an Album before its Artist
The Album needs artist_id, and the database cannot satisfy that foreign-key relationship if the Artist has not been inserted.
Fix: Insert or ignore the Artist first, select its primary key, and use that value in the Album insert.
Inserting a Track before its Album
Track uses album_id to link to Album, so the parent Album must already exist.
Fix: Complete the Album insert and SELECT step before inserting the Track.
Stopping after INSERT OR IGNORE
The next insert needs the concrete artist_id value.
Fix: Immediately select and store the primary key after the insert attempt.
Copying an artist name into the Album relationship field
The relationship is maintained by matching the Artist primary key with Album.artist_id.
Fix: Store the Artist primary key as artist_id.
Using the wrong insertion behavior for repeated Track data
The described insertion flow uses INSERT OR REPLACE for Track so repeated track data can update metadata such as rating, count, and length.
Fix: 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

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

Key Takeaways

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.