The Credits Roll

A hard 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
hard
Seniority
L4
Asked in
Wayfair

Problem

We run a streaming catalog where people search for films by title, by the actors in them, by the director, or by anyone else credited on the production, and also browse by genre. The same person is often credited more than once on one film, say as both its writer and its director, and every acting credit carries the character played and a billing order so the top-billed cast surface first. Design the data model behind this catalog, and outline how you would build the search architecture on top of it.

Searchable from every angle. Design it so nothing gets lost.

Worked solution and explanation

Why this problem exists in real interviews

This is a many-to-many with role attributes wearing a movie-catalog costume, and it hides a second trap: multi-attribute search at scale does not live in SQL. Persons appear on many titles in different roles, and a candidate who collapses role into a scalar on the person loses the entire query plan. Get the role placement wrong and an actor who later directs splits into two person rows, doubling them in every search result.

Break down the requirements

Step 1: Separate titles and persons

Both are first-class entities. A person has a stable identity independent of their filmography, and a title has a stable identity independent of its cast. Two dimensions, one junction.

Step 2: Model role on the junction

person_title_roles sits at the grain of one row per person per title per role, so the same person can be credited as writer and as director on one film without duplication. You can key this two ways: a composite PK on (person_id, title_id, role), or a surrogate PK alongside those three columns. Both capture the grain; the composite form makes it self-documenting, the surrogate form is friendlier to foreign keys pointing at the junction. What is NOT defensible is dropping role out of that grain.

Step 3: Separate genres into their own junction

Genres are also many-to-many with titles. Conform genres as their own small dimension with a title_genres junction rather than stuffing them into an array column on titles.

Step 4: Push search to a dedicated engine

Full-text and multi-attribute search at scale is not a SQL LIKE problem. The relational model is the source of truth; a search index (Elasticsearch, OpenSearch) is a denormalized projection rebuilt from change streams on these tables.

The solution

Below is one defensible design: two dimensions, two junctions, and a note that the search index is a projection of this relational model. Here the junction is shown with a composite key across its three grain columns; a surrogate key beside those same three columns is equally acceptable.

Grain of `person_title_roles` is one row per (`person_id`, `title_id`, role); one person can hold multiple roles on one title.
  • titles: title_id BIGINT PK, name TEXT, release_year INT, runtime_minutes INT, content_type TEXT
  • persons: person_id BIGINT PK, full_name TEXT, birth_year INT, primary_profession TEXT
  • person_title_roles: person_id BIGINT PK, title_id BIGINT PK, role TEXT PK, character_name TEXT, credit_order INT
  • genres: genre_id INT PK, genre_name TEXT
  • title_genres: title_id BIGINT PK, genre_id INT PK
  • titles references person_title_roles
  • persons references person_title_roles
  • titles references title_genres
  • genres references title_genres

The analysis pattern

Titles matching an actor search
SELECT
    t.name,
    t.release_year,
    STRING_AGG(DISTINCT g.genre_name, ', ') AS genres,
    STRING_AGG(DISTINCT ptr.role, ', ') AS roles
FROM titles t
JOIN person_title_roles ptr ON ptr.title_id = t.title_id
JOIN persons p ON p.person_id = ptr.person_id
LEFT JOIN title_genres tg ON tg.title_id = t.title_id
LEFT JOIN genres g ON g.genre_id = tg.genre_id
WHERE p.full_name = 'Tilda Swinton'
GROUP BY t.name, t.release_year
ORDER BY t.release_year DESC

Trade-offs and alternatives

Relational with search index projection
Strong relational integrity, clean updates, search index is a materialized view. Cost: two systems to keep in sync via CDC.
Document store as source of truth
Denormalized document per title with embedded cast and genres. Fast single-document reads. Cost: updating one actor across every title they ever appeared in is a fan-out write, and referential integrity is the application's problem.

Common follow-up questions