Creating and Querying Relationships Between Tables
A relationship is the connection between rows in different tables created by matching numeric IDs. The ID stored in one table points to the row with the same ID in another table.
The Repetition Problem
Imagine a music database with a Track table. A straightforward design might store the artist's full name in every track row. If several tracks belong to The Beatles, the text The Beatles appears repeatedly in the same column. This looks convenient at first, because the artist name is visible beside each track, but it creates redundant data and violates the fundamental rule of database normalization: do not repeat the same string data in a column more than once.
Normalization is the process of recognizing repeated data, moving that data into a separate table, storing it once, and replacing repeated strings with numeric references.
The Numeric-Key Design
The normalized design separates artist information from track information. The Artist table contains one row for each artist and gives that artist a unique numeric ID. The Track table does not repeat the artist's full name. Instead, each track stores the artist's numeric ID. The number is the reference that connects the track row to the corresponding artist row.
A relationship is the connection between rows in different tables created by matching numeric IDs. The ID stored in one table points to the row with the same ID in another table.
Splitting the Tables
Moving artist information out of Track
A music database needs to record tracks and their artists without repeating an artist name in the Track table.
- 1Identify the repeated value
The artist name is string data that can appear in multiple track rows.
- 2Create the separate table
Create an Artist table and give each artist one row with a unique numeric ID.
- 3Replace the repeated text
Store the artist's numeric ID in each related Track row instead of storing the full artist name there.
- 4Follow the relationship
To find the artist for a track, match the Track row's
artist_idwith the ID in the Artist table.
The artist information is stored once, while multiple track rows can refer to it through the same numeric ID.
Following a Relationship
A related-table query starts with data in one table and uses its numeric key to find matching data in another table. For example, a Track row for Let It Be can contain artist_id set to 1. The database follows that value to the Artist row whose ID is 1, where it finds The Beatles. The resulting view can show the track and artist together even though the artist name is stored only in the Artist table.
The relationship does not require copying the artist name into the Track table. The numeric ID is enough to locate the artist row when the related information is needed.
Benefits Beyond Storage
Separating repeated data makes updates simpler. If an artist changes their name, the normalized design requires one update in the Artist table. Every track that refers to that artist ID then uses the updated artist information. In a design that repeats the artist name in every Track row, each related row would need to be found and updated, creating a risk that some rows would be missed or would contain inconsistent text.
Normalization can also save storage space. Storing The Beatles once and referring to it with numeric IDs uses less space than storing the same string many times. This becomes more significant as a database grows to millions of rows. The separated structure also makes the data easier to query and maintain because each piece of information has a clearer home.
Mistakes in Table Design
Common mistake Repeating the full artist name in every Track row
What it looks likeSeveral rows each store The Beatles in the artist column.
Why it failsThe same string is repeated, which creates redundancy and makes later updates harder to keep consistent.
Do this insteadStore the artist once in an Artist table and place its numeric ID in the related Track rows.
Common mistake Treating the numeric ID as unrelated data
What it looks likeA Track row contains artist_id = 1, but the design does not use that value to find an Artist row.
Why it failsThe relationship exists because the numeric ID matches a row in another table. Without using the match, the reference cannot connect the related information.
Do this insteadMatch the ID in the Track row with the corresponding ID in the Artist table.
Common mistake Updating only one repeated string
What it looks likeOne Track row is changed after an artist changes their name, while other rows still contain the old name.
Why it failsRepeated copies can become inconsistent.
Do this insteadKeep the artist name in one Artist row so the artist information is updated in one place.
Apply the Pattern
A Track table contains several rows by the same artist. Describe how you would redesign the tables so the artist's name is stored once, explain what value belongs in each Track row, and describe how a query could recover the artist name for a selected track.
Hints
- First identify the string that is repeated.
- Create a separate table with one row for the artist and a unique numeric ID.
- Replace each repeated artist name in Track with that numeric ID.
- Follow the matching ID from Track to Artist when producing the result.
Key Takeaways
- 1Normalization avoids repeating the same string data in a column.
- 2Repeated information can be moved into a separate table and stored once.
- 3Numeric IDs replace repeated strings and identify matching rows in another table.
- 4A relationship connects rows across tables when their numeric IDs match.
- 5Normalized designs simplify updates, reduce storage needs, and help prevent inconsistent data.
At a glance
- Do not repeat the same string data in a column when it represents one shared fact.
- Store shared data in its own table and assign it a unique numeric ID.
- Place the numeric ID in related rows instead of repeating the full string.
- A matching numeric ID creates the relationship between rows in separate tables.
- Queries can follow that relationship to present related information together.