When Data Refuses to Fit in Neat Rows
The relational model handles one-to-many relationships elegantly: put a foreign key on the many side. But the real world is full of relationships that are not one-to-many. Students enroll in many courses and courses have many students. Actors appear in many films and films have many actors. Products belong to multiple categories and categories contain multiple products. These many-to-many relationships require a different modeling approach, and the patterns for handling them are some of the most important in schema design.
The Junction Table Pattern
A many-to-many relationship cannot be expressed with a simple foreign key in either table without violating 1NF. The solution is a junction table: a third table with two foreign keys, one pointing to each side of the relationship. A student_courses table has student_id and course_id. Each row represents one enrollment. The junction table can carry its own attributes that belong to the relationship itself: grade, enrollment_date, and seat_number belong on student_courses, not on students or courses.
Recognizing attributes that belong on junction tables versus entity tables is one of the most important modeling skills. A grade does not belong to the student alone (students have different grades in different courses) or to the course alone (courses have different students with different grades). It belongs to the enrollment relationship. Misplacing it means either duplicating data or losing information.
The most common interview answer for many-to-many: create a junction table. The follow-up that separates good from great: what attributes belong on the junction table versus the entity tables? Grade belongs on enrollments. Name belongs on students.
Self-Referencing Relationships
Some tables reference themselves. An employees table where each employee has a manager_id that references employees.id. A comments table where each comment has a parent_comment_id. A categories table where each category has a parent_category_id. These self-referencing foreign keys create tree and graph structures inside flat relational tables.
The elegance of self-referencing foreign keys is that they use the same relational machinery you already understand. The challenges are recursive queries (you need WITH RECURSIVE or application-level loops to find all descendants) and cycle prevention (if A manages B and B manages A, who is whose manager?). SQL:1999 added recursive CTEs to address the query challenge. Preventing cycles requires either application logic or a trigger that validates the hierarchy before each insert.
The Social Network act in the LLD Lab builds a friend request system that is a self-referencing many-to-many: both sides of the friendship are users in the same table. A canonical ordering trick (always store the lower user_id as friend_id_1) prevents duplicate rows representing the same friendship from opposite directions.
Polymorphic Associations: The Hard Problem
A notification can be about a post, a comment, a friend request, or a photo. A comment can belong to a blog post, a product review, or a support ticket. How do you model a foreign key that points to different tables depending on the row? Three patterns emerged from years of production experience.
The polymorphic column approach stores a type discriminator alongside a generic ID: commentable_type = 'BlogPost', commentable_id = 42. This is the Rails active record polymorphic association pattern. It is compact and flexible. It is also impossible to enforce with a foreign key constraint, meaning the database cannot prevent commentable_id 42 from pointing to a non-existent BlogPost. The integrity guarantee is gone.
The exclusive belongs-to approach uses multiple nullable foreign keys: blog_post_id, product_review_id, support_ticket_id. Exactly one is populated, the rest are null. This maintains foreign key integrity. It becomes unwieldy when the list of possible parent types grows.
The extension table approach uses a shared parent table for common fields and type-specific child tables that reference the parent. A content table has id, type, created_at. A posts table has content_id foreign key plus post-specific columns. A comments table has content_id foreign key plus comment-specific columns. Foreign keys are enforced everywhere. Queries require joins but are fully integrity-controlled.
- Junction tables answer many-to-many. The question is always what attributes belong on the junction itself.
- Self-referencing tables model hierarchies and graphs. Recursive CTEs are the SQL tool for traversing them.
- For polymorphic relationships: extension tables maintain integrity, polymorphic columns are flexible but lose FK guarantees. Choose based on whether integrity or flexibility is more important.
- In interviews: describe the trade-offs explicitly. Choosing polymorphic columns because you understand the FK trade-off is fine. Choosing them without knowing the trade-off is a red flag.
The Hidden Complexity of Junction Table Attributes
Junction tables that carry their own attributes reveal a fundamental modeling question: is this relationship itself a domain entity? A student_courses table with just (student_id, course_id) is a pure relationship. A student_courses table with (student_id, course_id, grade, enrolled_at, dropped_at, seat_number) is closer to an enrollment entity that happens to connect students and courses. Once a junction table has attributes that matter to the business, it often deserves its own name and natural key. Call it enrollments, not student_courses. Give it its own primary key. Let other tables reference it.
The distinction matters at scale. An enrollment record that other tables reference (grade appeals, enrollment receipts, attendance tracking) needs stability. A pure junction row that can be deleted and re-created without consequence is structurally different. Recognizing when a junction table has crossed into entity territory is a modeling skill that separates good schemas from great ones.
Recursive Queries in Practice
PostgreSQL's WITH RECURSIVE syntax handles tree traversal in a single query. The structure is always: anchor query (the root), UNION ALL, recursive query (children of the previous level), and a termination condition (no more children). Finding all subordinates of an employee ID: start with that employee, recursively join employees on manager_id = previous_level.id, stop when no more employees match. The result set contains every employee in the reporting chain at any depth.
Cycle detection is the critical safety concern. If employee A manages B and B manages A (accidentally or maliciously), the recursive query never terminates. PostgreSQL's recursive CTEs support CYCLE detection clauses (added in PostgreSQL 14) that automatically stop when a cycle is detected. Before PostgreSQL 14, the standard approach was tracking the path as an array and stopping when the next ID already appeared in the path.
Practical Guidance on Polymorphic Patterns
- Extension table pattern: create a parent content table with shared fields. Each concrete type (posts, comments, photos) has its own table with a foreign key to content. Full referential integrity maintained.
- Polymorphic column pattern (Rails style): commentable_type + commentable_id. Flexible, no FK constraints. Use when the list of types is large or frequently changing and integrity is less critical.
- Exclusive belongs-to: multiple nullable FK columns, exactly one populated. Verbose but fully integrity-controlled. Best for a small fixed list of possible parent types.
- When polymorphism appears in an interview: state the trade-off explicitly. 'The extension table maintains FK integrity but requires joins. The polymorphic column pattern is simpler but loses database-level integrity.'
- Avoid mixing polymorphic patterns in the same schema. Choose one and apply it consistently. Mixed patterns are confusing to maintain.
The Hidden Complexity of Junction Table Attributes
Junction tables that carry their own attributes reveal a fundamental modeling question: is this relationship itself a domain entity? A student_courses table with just (student_id, course_id) is a pure relationship. A student_courses table with (student_id, course_id, grade, enrolled_at, dropped_at, seat_number) is closer to an enrollment entity that happens to connect students and courses. Once a junction table has attributes that matter to the business, it often deserves its own name and natural key. Call it enrollments, not student_courses. Give it its own primary key. Let other tables reference it.
The distinction matters at scale. An enrollment record that other tables reference (grade appeals, enrollment receipts, attendance tracking) needs stability. A pure junction row that can be deleted and re-created without consequence is structurally different. Recognizing when a junction table has crossed into entity territory is a modeling skill that separates good schemas from great ones.
Recursive Queries in Practice
PostgreSQL's WITH RECURSIVE syntax handles tree traversal in a single query. The structure is always: anchor query (the root), UNION ALL, recursive query (children of the previous level), and a termination condition (no more children). Finding all subordinates of an employee ID: start with that employee, recursively join employees on manager_id = previous_level.id, stop when no more employees match. The result set contains every employee in the reporting chain at any depth.
Cycle detection is the critical safety concern. If employee A manages B and B manages A (accidentally or maliciously), the recursive query never terminates. PostgreSQL's recursive CTEs support CYCLE detection clauses (added in PostgreSQL 14) that automatically stop when a cycle is detected. Before PostgreSQL 14, the standard approach was tracking the path as an array and stopping when the next ID already appeared in the path.
Practical Guidance on Polymorphic Patterns
- Extension table pattern: create a parent content table with shared fields. Each concrete type (posts, comments, photos) has its own table with a foreign key to content. Full referential integrity maintained.
- Polymorphic column pattern (Rails style): commentable_type + commentable_id. Flexible, no FK constraints. Use when the list of types is large or frequently changing and integrity is less critical.
- Exclusive belongs-to: multiple nullable FK columns, exactly one populated. Verbose but fully integrity-controlled. Best for a small fixed list of possible parent types.
- When polymorphism appears in an interview: state the trade-off explicitly. 'The extension table maintains FK integrity but requires joins. The polymorphic column pattern is simpler but loses database-level integrity.'
- Avoid mixing polymorphic patterns in the same schema. Choose one and apply it consistently. Mixed patterns are confusing to maintain.