Skip to content
LLD Learn/Intermediate Modeling Patterns
Browsing as a guest. Sign in to save your progress and earn XP as you complete chapters.

Self-Referencing & Graph Relationships

4 min read

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).

A directional self-referencing relationship
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.

A canonical-ordering convention avoids storing each friendship twice
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.

Ready to Build This?

Design The Harvard Face Book and Friend Requests in the LLD Lab's Social Network act.

Ready to Build This?

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.

Sign In to ScaleDojo

No comments yet. Be the first to start the thread!