Designing Normalized Database Schemas

From Separate Tables to Complete Context

A normalized database stores related facts in separate tables rather than placing every detail in one repeated table. That separation is useful only if the database can reconstruct the relationships when you need to read the data. JOIN and ON clauses perform that reconstruction by following connections between foreign keys and primary keys.

The central idea is simple: a primary key identifies a row in its own table, while a foreign key stores a primary-key value from a related table. In the Album table, album id identifies an album, and artist_id points to the related row in the Artist table. The foreign key is the bridge that lets a query recover the artist associated with an album.

Following the Matching Keys

A JOIN combines rows from two tables. The ON clause tells the database which rows are related. In the common case, the condition compares a foreign key in one table with a primary key in another table. Only rows satisfying that condition are combined.

Suppose an Album row contains artist_id equal to 7. The query compares that value with the id values in Artist. The Artist row whose id is 7 supplies the artist columns for the combined result. The database is not guessing based on text; it is following the explicit key comparison in the ON condition.

One Artist, Many Albums

This relationship determines the shape of the JOIN result. If one artist is connected to three albums, the artist's information can appear in three result rows: one row for each connected album. The repeated artist values do not mean that three artist records were stored. They mean that the one parent row was combined separately with each matching child row.

Writing the Reconstruction Query

The basic JOIN pattern selects the columns you want, names a starting table, names a related table with JOIN, and states the relationship with ON. The ON condition should express the key comparison that connects the tables.

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

In this generated example, SELECT requests artist and album columns. FROM identifies Artist as the starting table. JOIN adds Album. ON states that an album belongs with the artist whose primary-key value equals the album's foreign-key value. Because one artist can have many albums, the result can contain several rows with the same artist information.

The result contains one row for each matching Artist and Album pair.

Reconstructing a Three-Level Relationship

The same reasoning extends across more than two tables. Consider Artist, Album, and Track. One artist has many albums, and one album has many tracks. A query that joins all three tables follows both relationships. Each result row represents one track, while also carrying the album and artist context connected to that track.

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

The first ON condition connects Album to Artist. The second connects Track to Album. The result is reconstructed progressively: a track is connected to its album, and that album is connected to its artist. This is why the final row can contain the full context of the track even though the underlying information remains distributed across normalized tables.

Avoiding Broken Relationships

• Treating a one-to-many JOIN as if it must return one row per parent.

  A one-to-many relationship produces one result row for each connected child record.

  Fix: Trace the child table and count the matching foreign-key values. Multiple matching child rows produce multiple result rows.

• Matching unrelated columns instead of the foreign key and primary key.

  The ON condition is supposed to identify related rows through the defined key relationship.

  Fix: Compare the foreign-key column with the corresponding primary-key column.

• Assuming a foreign key identifies a unique child row.

  Each child row points to one parent, but multiple child rows can point to the same parent.

  Fix: Distinguish the direction of the relationship: one Artist can have many Albums.

• Ignoring primary-key uniqueness and foreign-key indexes when designing related tables.

  Primary-key uniqueness is essential for correct JOIN behavior, and without foreign-key indexes, matching can require scanning the entire table and become very slow.

  Fix: Use uniqueness constraints on primary keys and indexes on foreign keys.

Practice the Row Trace

The Reconstruction Pattern

1. A primary key uniquely identifies a row in its table, while a foreign key stores a related primary-key value.
2. JOIN combines rows from tables, and ON states which key values must match.
3. A one-to-many relationship produces one result row for each matching child record.
4. Joining Artist, Album, and Track follows two relationships to reconstruct the full context of each track.
5. Primary-key uniqueness supports correct matching, and foreign-key indexes help JOIN queries locate matches efficiently.