Databases are like the glue that holds information together. Previously, you explored how relational database tables store data in rows and columns, with unique identifiers like PRIMARY KEYs to distinguish each row. Now, it’s time to take it a step further and understand how different tables relate to each other.
Imagine a library. The books are stored in one section, and the authors’ details in another. The relationship between a book and its author connects these sections, creating a meaningful system. This connection is where relational databases shine.
A PRIMARY KEY is like a fingerprint—it uniquely identifies each row in a table. To connect tables, we use a FOREIGN KEY, which references the PRIMARY KEY in another table. Think of it as a bridge linking two islands of information.
By convention, the FOREIGN KEY often combines the name of the related table and its PRIMARY KEY. For instance, consider a database tracking people and their jobs:
people table:
ssn first_name last_name
123-45-6789 John Doe
987-65-4329 Jane Doe
987-65-4320 John Smith
jobs table:
id job_name start_date end_date person_ssn
1 Bookkeeper 1997-03-05 2004-09-30 123-45-6789
2 Janitor 2000-01-04 987-65-4329
3 Marketer 2002-11-12 2005-12-20 987-65-4320
4 Accountant 2004-10-01 123-45-6789
Here, person_ssn in the jobs table is the FOREIGN KEY, linking jobs to individuals in the people table via their ssn (the PRIMARY KEY).
In relational databases, there are three main types of relationships:
This type is like assigning one locker to one student. Each record in one table corresponds to one record in another table. For instance, user preferences can be stored separately from their login credentials:
accounts table:
id username password
1234 John.Doe (encrypted)
9876 Jane.Doe (encrypted)
4200 John.Smith (encrypted)
preferences table:
account_id language color_mode
1234 English (US) light
9876 English (UK) dark
4200 English (AU) dark
Here, account_id in the preferences table is both the PRIMARY KEY and a FOREIGN KEY referencing the accounts table.
This is like a tree where one trunk branches into many limbs. For example, in the people and jobs tables, one person can have multiple jobs, but each job belongs to one person:
people table:
ssn first_name last_name
123-45-6789 John Doe
987-65-4329 Jane Doe
jobs table:
id job_name person_ssn
1 Bookkeeper 123-45-6789
2 Janitor 987-65-4329
This is like a group project where each member contributes to multiple tasks, and each task has multiple contributors. For instance, imagine tracking which books people have read:
people table:
id name
1 Alice
2 Bob
books table:
id title
101 "Moby Dick"
102 "1984"
people_books join table:
person_id book_id
1 101
1 102
2 101
Here, the people_books table acts as a bridge, storing FOREIGN KEYS from both people and books tables to connect them.
Relationships enable powerful queries. Instead of storing duplicate data across tables, we use relationships to connect relevant pieces. This approach is not only efficient but also reduces errors. It’s like having a centralized library catalog that tells you which section to visit for a specific book.
In this tutorial, you explored the essence of database relationships. You learned:
As you continue your database journey, remember that relationships are the backbone of relational databases. Mastering them is key to designing efficient, scalable systems.