Menu

A Tiny Data Model for a Daily Puzzle Archive
📰

A Tiny Data Model for a Daily Puzzle Archive

DEV Community: database·Alex Rivera·about 6 hours ago
#l8hHKAiI
#devtodatabase#devto#dev#daily#archive
Long article (383 words) — no AI summary yet.
Reading 0:00
15s threshold

Every daily-content site eventually grows an archive, and every archive eventually grows a data model that nobody planned. I have been maintaining a companion site for a daily music guessing game, and the archive schema is the part I keep rewriting. Here is the smallest version that survived contact with a few hundred days of content.

The core record is one row per daily edition: date, edition identifier, and the answer payload. The payload is the part people get wrong. It is tempting to model the answer as a single string, but a music puzzle has three or four lanes, each with its own answer, plus hints at several levels of specificity. A single string forces the hints into a second table and the lanes into a third, and then every query joins three things to render one page.

The version that worked keeps the answer payload as a document: one nested structure per edition, with lanes, hint ladder, and metadata (era, genre, artist references) inside it. It is denormalised on purpose.…

Continue reading — create a free account

Join HashtagPLUS to read full articles, follow hashtags, vote, and join the conversation.