Dev.to WebDev 🛠 Dev 👁 0 📖 2 min read

A Tiny Data Model for a Daily Puzzle Archive

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

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. Daily archives are read-heavy and write-once, so denormalisation costs nothing and buys a single-page render from a single fetch. The hints stop being a table and become a list on the record, which also makes their order explicit instead of implied by a sort column.

The second design decision is the edge direction. The archive has two kinds of relationships: chronological (yesterday, tomorrow) and thematic (same artist, same era, same genre). Do not encode both as foreign keys on the daily record. Encode the chronological links, and derive the thematic ones from indexed metadata at build time. The thematic relations are many-to-many and will drag the schema toward a graph if you let them.

The third decision is slugs. Every edition needs a stable URL that survives a title change, because the answer text will change when someone fixes a typo. Use the date as the primary slug, and keep a separate human-readable slug as a redirect alias. Canonical points at the date-based URL. This one rule has prevented more broken inbound links than any other change I have made. If you want to see the shape in practice, the archive on the site is built exactly this way, and the practice mode reads the same records through a different query.

📰 Read the original article on Dev.to WebDev

Originally published by Dev.to WebDev. Aggregated on AIWithGhost for educational purposes — full credit and traffic to the original publisher.