Database Normalization and Design Principles
A primary key is a unique integer identifier (conventionally named 'id') that uniquely identifies each row within a table.
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?
Locate the table: The value 2 is being interpreted within the Artist table.
Match the identifier: Find the row in that table whose id value is 2.
Identify 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?
Read the foreign key: The Track row stores the integer 7 in its artist_id column.
Search the referenced table: Look in the Artist table for a row whose primary-key column, id, contains 7.
Follow 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
Treating a foreign key as an unrelated number
The foreign key stores a primary-key value from another table, so its meaning comes from the matching row in that referenced table.
Fix:
Use the foreign-key value to find the row whose primary-key column contains the same integer.Looking for a matching value in the wrong column
The relationship is defined by matching the foreign-key integer with a primary-key integer.
Fix:
Compare the foreign key with the referenced table's id value.Assuming id is just a descriptive field
A primary key uniquely identifies the row within its table.
Fix:
Interpret id as the conventional primary-key column and use its unique integer to distinguish rows.Ignoring the table named by a foreign-key convention
The table_name_id convention helps make the intended relationship explicit and self-documenting.
Fix:
Use 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_id column.
- 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
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
- A primary key is a unique integer that identifies one row within a table.
- id is the conventional name for a primary-key column.
- A foreign key stores a primary-key value from another table.
- To trace a relationship, match the foreign-key integer with the referenced table's primary-key integer.
- Names such as artist_id make table relationships explicit and self-documenting.
Key Takeaways
- 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_id make relationships easier to understand and support consistent database organization.