Database Normalization and Design Principles
When related information is stored in separate tables, the database needs a reliable way to connect the rows.
Why Tables Need Connections
When related information is stored in separate tables, the database needs a reliable way to connect the rows. Imagine an Artist table containing artist names and eye color, alongside a Track table containing song titles and play counts. A reader can see the information in both tables, but still needs to know which track belongs to which artist. Primary keys and foreign keys provide that connection through integer values that act as references between tables.
Identifying Rows with Primary Keys
A primary key is a unique integer identifier for each row within a table. Its purpose is to distinguish one row from every other row in that same table. The conventional column name for a primary key is id. The name does not describe the subject-specific information in the row; it signals that the value serves as the row's identifier.
Reading a Primary Key
Suppose an Artist table contains three rows whose id values are 1, 2, and 3. What does the value 2 identify?
- 1Locate the table
The value 2 is being interpreted within the Artist table.
- 2Match the identifier
Find the row in that table whose id value is 2.
- 3Identify the row
That matching row is the one uniquely identified by the primary key value 2.
The integer 2 identifies one specific row in the Artist table. It is not merely an ordinary descriptive value; it is the row's unique identifier.
Following a Foreign Key
A foreign key is a column in one table that stores a primary-key value from another table. The stored integer creates a reference. To follow the reference, read the foreign-key value in the first table, move to the referenced table, and find the row whose primary-key value matches it.
Tracing Track Ownership
A Track row contains artist_id = 7. The Artist table contains rows with id values 5, 7, and 9. Which Artist row is connected to the Track row?
- 1Read the foreign key
The Track row stores the integer 7 in its
artist_idcolumn. - 2Search the referenced table
Look in the Artist table for a row whose primary-key column, id, contains 7.
- 3Follow the match
The Artist row with id = 7 is the row referenced by the Track row.
The Track row is linked to the Artist row whose primary key is 7.
Naming Relationships Clearly
Naming conventions make relationships explicit and self-documenting. A primary-key column is conventionally named id. A foreign-key column commonly combines the referenced table name with id, producing a form such as table_name_id. In the Artist and Track example, a column named artist_id communicates that the value refers to an Artist row.
| Column role | Common naming pattern | What the name communicates |
|---|---|---|
| Primary key | id | This value uniquely identifies a row in its table. |
| Foreign key | table_name_id | This value stores a primary-key value from the named table. |
Conventional names for identifying and linking rows
Mistakes in Key Tracing
Common mistake Treating a foreign key as an unrelated number
What it looks likeA Track row has artist_id = 7, but the value is read without checking the Artist table.
Why it failsThe foreign key stores a primary-key value from another table, so its meaning comes from the matching row in that referenced table.
Do this insteadUse the foreign-key value to find the row whose primary-key column contains the same integer.
Common mistake Looking for a matching value in the wrong column
What it looks likeThe value in artist_id is compared with an artist name instead of the Artist table's id column.
Why it failsThe relationship is defined by matching the foreign-key integer with a primary-key integer.
Do this insteadCompare the foreign key with the referenced table's id value.
Common mistake Assuming id is just a descriptive field
What it looks likeThe id value is treated like an ordinary piece of information rather than the row identifier.
Why it failsA primary key uniquely identifies the row within its table.
Do this insteadInterpret id as the conventional primary-key column and use its unique integer to distinguish rows.
Common mistake Ignoring the table named by a foreign-key convention
What it looks likeartist_id is read without noticing that its name points toward the Artist table.
Why it failsThe
table_name_idconvention helps make the intended relationship explicit and self-documenting.Do this insteadUse the column name as a clue, then verify the relationship by matching its value to the referenced table's primary key.
Practice the Reference Path
A Track table contains a row with artist_id = 12. The Artist table contains rows with id values 4, 8, 12, and 15. Describe the exact path you would follow to identify the related Artist row.
Hints
- Start with the integer stored in the Track row's
artist_idcolumn. - Search the Artist table's id column for the same integer.
- The matching id identifies the related Artist row.
What do you think happens?
The Track row stores artist_id = 12, and the Artist table has id values 4, 8, 12, and 15. Which Artist primary-key value will the foreign key select?
Reveal answer
12
A foreign key points to the referenced row by matching its integer value with the primary-key value in the other table.
Key Takeaways
- 1A primary key is a unique integer that identifies one row within a table.
- 2id is the conventional name for a primary-key column.
- 3A foreign key stores a primary-key value from another table.
- 4To trace a relationship, match the foreign-key integer with the referenced table's primary-key integer.
- 5Names such as
artist_idmake table relationships explicit and self-documenting.
At a glance
- Primary keys uniquely identify rows within their own table.
- The conventional primary-key column name is id.
- Foreign keys connect tables by storing a primary-key value from another table.
- Following a foreign key means finding the matching id in the referenced table.
- Conventional names such as
table_name_idmake relationships easier to understand and support consistent database organization.