Self-Referencing & Graph Relationships
You'll learn to
- -Model a self-referencing many-to-many relationship, like a friend or follow graph, in a relational schema
- -Handle symmetric (friendship) vs. asymmetric (follow) relationships with the right table shape
A self-referencing relationship is a many-to-many relationship from a table back to itself - the exact shape behind every social graph. It reuses the junction table technique from Phase 2's relational-modeling module, with one new wrinkle: both foreign keys in the junction table point at the same table.
Asymmetric: A Follow Graph
A "follow" relationship is directional - Alice following Bob does not imply Bob follows Alice. This maps directly onto a junction table with two foreign keys back to users, where the two columns have different meanings (follower vs. 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
);Reading "who does Alice follow" is a query filtering follower_id = alice_id; "who follows Alice" filters followee_id = alice_id - two genuinely different queries, which correctly reflects that the relationship itself is not symmetric.
Symmetric: A Mutual Friendship Graph
A "friendship," by contrast, is inherently mutual - if Alice is friends with Bob, Bob is friends with Alice, by definition. Storing this the same way as follows would either need two rows per friendship (Alice-Bob and Bob-Alice, doubling storage and risking the two falling out of sync) or a query-time convention of always checking both directions. A cleaner convention: always store the pair with the lower id first, so each friendship exists as exactly one canonical row.
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;Friend Requests: A Pending State Before the Symmetric Relationship Exists
A "friend request" is genuinely asymmetric (Alice requested; Bob has not yet accepted), which is a different, directional relationship that only becomes the symmetric friendships row once accepted - two separate tables for two separate concepts, rather than trying to force one table to represent both a pending, directional state and a confirmed, mutual one.
The CHECK (follower_id != followee_id) and CHECK (user_a_id < user_b_id) constraints are small but important - without them, nothing stops a "user follows themselves" row or a duplicate friendship stored in both column orders from being inserted.
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.