How Databases Actually Store Relationships Up until now, we’ve talked a lot about relationships in databases: - One-to-Many - Many-to-One - One-to-One - Many-to-Many But
MSMuhammad SufiyanSoftware Engineer · 4d ago
Backend Engineering HubT-
How Databases Actually Store Relationships
Up until now, we’ve talked a lot about relationships in databases:
One-to-Many
Many-to-One
One-to-One
Many-to-Many
But there’s one big missing piece:
👉 How do we actually store these relationships in a database?
👉 How does SQL connect rows across different tables?
This is where Primary Keys and Foreign Keys come in.
Why Do We Even Need Keys?
So far in the course, we’ve created tables like cities and phones, but we haven’t added anything that uniquely identifies a row.
In real-world databases, this is not optional.
📌 Every table must be able to uniquely identify each row
📌 Every relationship depends on this uniqueness
That’s exactly what keys are for.
Primary Key (PK)
A Primary Key is a column that uniquely identifies one single row in a table.
Key Properties of a Primary Key
A primary key:
Is unique
Never repeats
Never changes
Identifies exactly one row
Example: Photos Table
id
url
created_at
1
img1.jpg
2024-01-01
2
img2.jpg
2024-01-02
3
img3.jpg
2024-01-03
Here:
id is the primary key
No two rows can ever have the same id
If we ask for photo with id = 2, we will always get exactly one photo
Even if rows move around or sorting changes, the ID stays the same.
💡 Think of a primary key like a CNIC number or roll number — unique and permanent.
Every Table Has a Primary Key
From now on, assume this rule:
✅ Every table we create will have a primary key
Examples:
users.id
photos.id
comments.id
likes.id
Without a primary key, relationships become unreliable and messy.
Foreign Key (FK)
A Foreign Key is how tables connect to each other.
If a primary key identifies a row inside its own table,
a foreign key points to a row in another table.
One-to-Many Relationship (User → Photos)
We already learned:
A user has many photos
Now let’s store that in data.
Users Table
id
username
1
monahan93
2
pfeiffer
4
instagram
Here:
id is the primary key
Photos Table (with Foreign Key)
id
url
user_id
1
img1.jpg
4
2
img2.jpg
4
3
img3.jpg
1
Here:
id → primary key
user_id → foreign key
📌 user_id stores the ID of the user who owns the photo
How the Relationship Works
Photos with user_id = 4 belong to Instagram
Photos with user_id = 1 belong to monahan93
So when we say:
“These photos belong to this user”
What we actually mean is:
“These photos have a user_id that matches the user’s primary key”
Querying the Relationship
Get all photos for user with id = 4
SELECT *
FROM photos
WHERE user_id = 4;
This query works only because of the foreign key.
One-to-Many vs Many-to-One (Same Data!)
Important thing to understand:
From users → photos → One-to-Many
From photos → user → Many-to-One
Same tables.
Same data.
Different perspective.
💡 The database structure does not change — only how you think about it does.
Why Foreign Keys Are So Powerful
Foreign keys allow us to:
Link tables together
Maintain data consistency
Write JOIN queries
Represent real-world ownership
Without foreign keys:
Relationships would exist only in your head
SQL would have no way to connect records
Mental Model (Very Important)
Think like this:
Primary Key → “Who am I?”
Foreign Key → “Who do I belong to?”
Example:
users.id → Who is this user?
photos.user_id → Who owns this photo?
Summary
✔ Every table has a primary key
✔ Primary keys uniquely identify rows
✔ Foreign keys store another table’s primary key
✔ One-to-Many relationships are implemented using foreign keys
✔ Many-to-One is the same relationship, just viewed differently
What’s Next?
At this point:
You know all four relationship types
You know how relationships are stored
You understand PKs and FKs conceptually
👉 Next, we’ll look at more examples and then start writing real SQL queries using relationships and JOINs.
That’s where everything really starts to click 🔥
HIRINGMINE CAREER SIGNAL
This writing is proof of expertise.
Explore the author’s verified skills, projects and availability—or start a professional conversation.
Querying the Relationship