Self-Referencing & Graph Relationships
You'll learn to
- -Model a table that relates to itself, using a junction table whose two foreign keys point at the same table
- -Choose the right shape for a directional relationship (follow) and a mutual one (friendship)
- -Keep a mutual relationship from being stored twice, with a canonical ordering and a CHECK
A self-referencing relationship is one where a table relates to itself. Users follow users. Employees report to employees. It is the shape behind every social graph. It reuses the junction-table idea from the relational-modeling module, with one new twist: both foreign keys in the junction table point at the same table.
An Analogy: Who Is Connected to Whom
Picture a party where everyone wears a name tag. Two kinds of connection exist. Some are one-way, like a fan following a singer: the fan knows the singer, and the singer may not know the fan. Others are two-way, like a friendship: if Alice is Bob's friend, then Bob is Alice's friend, by definition. The two need different tables, because one-way links have a direction to record and two-way links must not be stored in both directions.
Asymmetric: A Follow Graph
"Follow" is directional: Alice following Bob does not mean Bob follows Alice. So the junction table has two foreign keys back to users, and the two columns mean different things: who is following (the follower) and who is being followed (the followee).
CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE follows (
follower_id INTEGER NOT NULL,
followee_id INTEGER NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (follower_id, followee_id),
FOREIGN KEY (follower_id) REFERENCES users(id),
FOREIGN KEY (followee_id) REFERENCES users(id),
CHECK (follower_id != followee_id) -- a user cannot follow themselves
);Take three rows: (Alice, Bob), (Alice, Cy) and (Cy, Alice). "Who does Alice follow?" filters follower_id = Alice, which gives Bob and Cy. "Who follows Alice?" filters followee_id = Alice, which gives Cy. Those are two genuinely different queries, which is correct, because the relationship itself is not symmetric.
Symmetric: A Mutual Friendship Graph
A friendship is mutual by nature. If you store it the way you store follows, you have two bad choices. You can write two rows per friendship, (Alice, Bob) and (Bob, Alice), which doubles storage and lets the two rows drift apart. Or you can check both directions in every query. A cleaner convention is to always store the pair with the smaller id first. Then each friendship is exactly one row, and the database can enforce the rule.
CREATE TABLE friendships (
user_a_id INTEGER NOT NULL, -- convention: always the smaller id
user_b_id INTEGER NOT NULL, -- convention: always the larger id
became_friends_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (user_a_id, user_b_id),
FOREIGN KEY (user_a_id) REFERENCES users(id),
FOREIGN KEY (user_b_id) REFERENCES users(id),
CHECK (user_a_id < user_b_id) -- enforces the canonical ordering
);
-- finding all of a user's friends requires checking both columns:
SELECT user_b_id AS friend_id FROM friendships WHERE user_a_id = :user_id
UNION
SELECT user_a_id AS friend_id FROM friendships WHERE user_b_id = :user_id;For example, users 7 and 3 become friends. The only row allowed is (3, 7), because the CHECK demands the first id be smaller. Trying to insert (7, 3) is rejected, so duplicates in the other order cannot happen. The price is that finding a user's friends must look in both columns, which is what the UNION query does.
Friend Requests: A Pending State Comes First
A friend request is directional: Alice asked, and Bob has not yet accepted. It only becomes a mutual friendship once he does. That is two concepts, so use two tables, a directional requests table and the symmetric friendships table. Do not force one table to represent both a pending, one-way state and a confirmed, two-way state.
The small constraints matter. CHECK (follower_id != followee_id) stops a user from following themselves, and CHECK (user_a_id < user_b_id) stops the same friendship being stored in both column orders.
Interview Signal is part of Pro
See a real weak answer next to a real strong one for this exact topic.
Quiz is part of Pro
Test what you just read with a short quiz, and bank the XP.
Design The Harvard Face Book and Friend Requests in the LLD Lab's Social Network act.
A self-referencing table (a friends table pointing back at users, an employees table pointing back at itself for manager_id) needs a self-referencing JOIN to query - SQL Lab's "Come Alone" is built around exactly that, table-aliased so the same table can play two roles in one query.
Discussion0
Join the Discussion
Sign in to leave comments, reply to others, or like insights.
No comments yet. Be the first to start the thread!