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.…