# The Endless Thread > Follows, likes, replies to replies. It never stops. Canonical URL: Domain: Data Modeling · Difficulty: medium · Seniority: L5 ## Problem We're building an analytics warehouse for a consumer social network where users share photos and video. Users create posts, follow one another one direction at a time, and like posts, and the model has to make a duplicate follow or a repeat like impossible to store on its own, not through application checks. A comment either sits on a post or replies to another comment, so with the growth team tracking virality and the product team tracking engagement per content type, design the warehouse model. ## Worked solution and explanation ### Why this problem exists in real interviews Social graphs are a probe for two concepts at once: self-referential relationships and many-to-many modeling via junction tables. The `follows` table is a user-to-user many-to-many, and `comments.parent_comment_id` is a self-reference for nesting replies. Interviewers watch for whether the candidate reaches for composite primary keys on the junctions rather than surrogate IDs. > **Trick to Solving** > > The tell is "users follow other users" plus "the model has to make a duplicate impossible to store on its own". Before drawing any tables, a strong candidate asks: can a user follow the same user twice? The answer is no, and there is no separate UNIQUE constraint to lean on here, so the junction table itself needs a composite primary key on (`follower_id`, `followed_id`). > > 1. Spot the self-referential many-to-many on users > 2. Use composite PKs on follows and likes > 3. Add a parent_comment_id self-reference on comments for nested replies > 4. Keep comments as a distinct entity, not a subtype of posts --- ### Break down the requirements #### Step 1: Identify the core entities Users, posts, and comments are entities with their own identity. Follows and likes are relationship tables, not entities, and live as junctions. #### Step 2: Model follows as a junction with composite PK `(follower_id, followed_id)` is the primary key, with both columns also FK to users. This makes a duplicate follow structurally impossible without relying on a secondary unique constraint the canvas cannot even express. #### Step 3: Handle likes the same way `(user_id, post_id)` is the composite PK on `likes`. A user either likes a post or does not; a second identical row is rejected at insert time by the key itself. Likes target a post, so the grain stays two-dimensional and no comment_id leaks into the table. #### Step 4: Self-reference comments for nested replies `comments.parent_comment_id` points at another comment within the same post. This supports replies to comments up to the UI's nesting depth without a separate threads table. Top-level comments leave it null. #### Step 5: Keep comments separate from posts Comments have different moderation rules, different cardinality, and different query patterns (by post, not by feed). Making them a distinct entity keeps both simple, and the parent_comment_id self-reference is what carries the reply thread. --- ### The solution Below is one defensible model. The composite primary keys on the junction tables are the anchor; they make duplicate prevention a schema-level guarantee rather than an application concern. The self-reference on comments carries the reply thread. > **Why This Design Works** > > Composite primary keys on junction tables turn a business rule (a user cannot follow the same person twice) into a database invariant. They also give you a natural clustered index for the common query patterns ("who does this user follow" and "who follows this user"). The cost is slightly bulkier indexes than a surrogate key would give. > **Interviewers Watch For** > > Strong candidates mark BOTH junction columns as the composite PK, which is the only way to bake the uniqueness in when there is no separate UNIQUE constraint available. They also thread comments through parent_comment_id rather than inventing a separate threads table. Weaker candidates add surrogate IDs to follows and miss the duplicate-prevention invariant. > **Common Pitfall** > > Adding a `follow_id` surrogate to the follows table, or a `like_id` plus a stray `comment_id` to likes. Both push duplicate-prevention out of the schema and into whatever the application remembers to check, and they smear an extra dimension across the like grain. Keep likes a clean (user_id, post_id) pair with both columns as the PK. --- ### The analysis pattern **Top creators by follower growth this week** ```sql 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 ``` --- ### Trade-offs and alternatives **Junction tables with composite PKs** Schema-level duplicate prevention, natural clustered indexes, simple queries. Cost: slightly bulkier indexes than surrogate-keyed alternatives and more effort if a relationship needs to carry many attributes. **Graph database backing store** Optimized traversal, native mutual-friend and recommendation queries. Cost: second storage system to operate, duplicate analytics layer, and most BI tools do not speak Cypher or Gremlin. --- ## Common follow-up questions - A celebrity has 100M followers. Does the follows table partitioning strategy hold up? _(Tests partitioning by followed_id to keep hot-user fanout queries tractable.)_ - Users can mute other users without unfollowing. Where does that relationship live? _(Tests whether the candidate adds a new junction table rather than widening follows.)_ - A deleted user's posts must disappear for other users but remain for moderators. How? _(Tests soft delete on posts and whether the query layer filters on a tombstone column.)_ - Comment threads go 50 levels deep. Does the parent_comment_id self-reference still work? _(Tests recursive CTE feasibility on parent_comment_id versus a materialized path or ltree alternative.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/the_endless_thread) - [Data Modeling Interview Questions](https://datadriven.io/data-modeling-interview-questions) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.