Data model for an editorial calendar

WHAT THIS TESTS: It evaluates whether you can translate an editorial workflow into a normalized schema that powers a date-bucketed calendar UI efficiently and stays maintainable as the team grows.

A GOOD ANSWER COVERS: Model a content_pieces entity holding the title, the body or a reference to it, a target_publish_date, optionally a separate published_at, and a status. Status should be a constrained enum or a lookup table, not free text, with values like draft, in_review, scheduled, and published, because the workflow drives transitions and filtering. Authorship is a relationship: a single-author model uses an author_id foreign key to an authors table; if pieces can have multiple contributors or roles, use a join table content_authors with a role column. The calendar view queries pieces whose publish_date falls in a visible range, so add an index on publish_date and often a composite index on status plus publish_date. Consider a separate audit or history table to track status changes over time for accountability.

COMMON WRONG ANSWERS: Storing the author as a plain string, which breaks reassignment and reliable filtering. Status as arbitrary text with no constraint, allowing typos and inconsistent filters. A single wide denormalized table that cannot represent multiple authors. No date index, which makes the calendar slow once content volume grows.

LIKELY FOLLOW-UPS: How do you support multiple authors or editors each with a role. How do you query a single month efficiently. How do you handle the scheduled-versus-published distinction cleanly. Where do you store the workflow history so editors can audit who moved what and when.

ONE CONCRETE EXAMPLE: To render March, the UI runs a query selecting from content_pieces where target_publish_date is between March 1 and March 31, joined to authors for display names, ordered by date. With an index on target_publish_date this returns instantly even at scale, and the status enum lets the front end color each calendar entry by its workflow state so editors see progress at a glance.