The Endless Thread

A medium Data Modeling interview practice problem on DataDriven. Design the schema on the canvas: tables, keys and relationships, then get feedback on it.

Domain
Data Modeling
Difficulty
medium
Seniority
L5

Problem

Dashboards for a photo-and-video social network keep showing the same account following one creator twice and the same user liking one post twice, so the engagement team wants a warehouse model where a follow is identified by exactly who follows whom and a like by exactly which user liked which post, making either duplicate impossible to store without any application checks. Users create posts that are each a photo or a video, follow one another one direction at a time, and like posts, while a comment either sits on a post or replies to another comment. Design the model so the product team can report engagement per content type.

Follows, likes, replies to replies. It never stops.

Worked solution and explanation

What this problem really is

Strip the social-network costume and this is two many-to-many relationships plus one self-reference, and the whole design is decided by how you key the junctions. follows relates users to users, likes relates users to posts, and comments.parent_comment_id points back at comments. Anyone can draw five boxes. What separates candidates is the junction key: leave the junction keyless, or reach for a surrogate follow_id or like_id, and nothing stops the same (follower_id, followed_id) pair from landing twice. The prompt says a follow is identified by who follows whom and duplicates must be impossible to store without any application checks, and only a primary key on the pair itself can promise that.

Building the model

Step 1: Separate entities from relationships

users, posts, and comments own their identity, so each gets a surrogate key (user_id, post_id, comment_id). follows and likes are not entities, they are relationships, so they become junction tables keyed on the participants themselves, never on a fresh invented id.

Step 2: Put the content type on the post

Every post is a photo or a video, and the report is engagement per content type, so posts carries a content_type holding 'photo' or 'video'. It lives on the post, not on likes or comments: an engagement row inherits its content type through post_id, so the fact is stored once.

Step 3: Model follows as a self-referential junction

follows relates users to users, so follower_id and followed_id are both foreign keys to users.user_id, and together they form the composite primary key. That pair is what makes following someone twice impossible. Direction matters: follower_id acts, followed_id is followed, so the edge stays one-directional.

Step 4: Key likes on the pair, not an id

Same shape, different pair. likes has user_id and post_id as its composite primary key, each also a foreign key. A second identical row is rejected by the key itself. Keep it a clean pair with no stray comment_id, so the grain stays one row per user per liked post.

Step 5: Self-reference comments for threads

A comment either sits on a post or replies to another comment, so comments carries both post_id and a nullable parent_comment_id that points back at comments.comment_id. Top-level comments leave parent_comment_id null; replies point at their parent. No separate threads table is needed.

On `follows` and `likes` the two referencing columns together form the composite primary key, so a duplicate follow or like cannot be stored; `posts.content_type` marks photo or video, and `comments.parent_comment_id` self-references `comments` to thread replies.
  • users: user_id BIGINT PK, handle TEXT, created_at TIMESTAMP, country TEXT
  • posts: post_id BIGINT PK, user_id BIGINT FK, content_type VARCHAR, body TEXT, created_at TIMESTAMP, is_deleted BOOLEAN
  • follows: follower_id BIGINT PK, followed_id BIGINT PK, followed_at TIMESTAMP
  • likes: user_id BIGINT PK, post_id BIGINT PK, liked_at TIMESTAMP
  • comments: comment_id BIGINT PK, post_id BIGINT FK, user_id BIGINT FK, parent_comment_id BIGINT FK, body TEXT, created_at TIMESTAMP
  • posts.user_id references users.user_id
  • follows.follower_id references users.user_id
  • follows.followed_id references users.user_id
  • likes.user_id references users.user_id
  • likes.post_id references posts.post_id
  • comments.user_id references users.user_id
  • comments.post_id references posts.post_id
  • comments.parent_comment_id references comments.comment_id

The product team's report falls straight out of this shape. Because a like and a comment each reach exactly one post, and the post carries content_type, engagement rolls up per content type without double counting: count each side per post first, then sum.

Engagement per content type
SELECT
    u.handle,
    COUNT(*) FILTER (WHERE f.followed_at >= NOW() - INTERVAL '7 days') AS new_followers,
    COUNT(*) AS total_followers,
    COUNT(DISTINCT p.post_id) AS posts_this_week
FROM users u
JOIN follows f ON f.followed_id = u.user_id
LEFT JOIN posts p
    ON p.user_id = u.user_id
   AND p.created_at >= NOW() - INTERVAL '7 days'
GROUP BY u.handle
HAVING COUNT(*) FILTER (WHERE f.followed_at >= NOW() - INTERVAL '7 days') > 0
ORDER BY new_followers DESC
LIMIT 50

Pre-counting likes and comments per post keeps the two fan-outs from multiplying each other before the roll-up by content_type.

Junction tables with composite keys
Duplicate prevention is a schema invariant, counting rows in follows is cheap, and every BI tool already speaks SQL. Cost: the composite index is a little bulkier than a surrogate, and a relationship that grows many attributes gets awkward.
Graph database backing store
Native traversal makes mutual-follow and recommendation queries fast. Cost: a second storage system to run, a duplicate analytics layer, and most warehouses and BI tools do not speak Cypher or Gremlin.

Common follow-up questions