Test both schemas with a seeded simulation of five years of museum activity

Context

The assignment only asked for a design; no data came with it, and real visitor, payment or booking records must never be used. Testing a schema needs data that exercises every rule in the brief: re-entry on the same day, guides returned at a different wing, guides still in service, cash only at the desks, exhibition slots filling up. It also has to be reproducible, so that every number on the site (the replay, the ten answers, the index timings) can be regenerated and checked.

Decision

A deterministic simulation in TypeScript generates five years of activity (1 March 2015 to 29 February 2020) from one seed, 20200403, the brief's due date. It simulates parties of visitors day by day (seasonal, weekday and yearly factors, about 1.9 parties a day plus occasional school groups), and derives every row from those visits, so the tables agree with each other by construction. Random numbers come from sfc32 seeded through splitmix32, with an independent child stream per concern, so adding draws in one place does not shift another. Only the brief's fixed facts are real: the five entrances, three wings, 13 languages, the prices, the slot size and the Leonardo da Vinci exhibition.

Options considered

1. Hand-written fixtures. Precise, but too small to find the key collisions that break the 2020 tables, and slow to extend.
2. Independent random rows per table (Faker style). Quick, but rows would not agree across tables (a wing scan for a ticket that never entered), so the schemas would be tested against nonsense.
3. A seeded simulation that derives all tables from simulated visits. Chosen.
4. Ask a language model to write the data. No budget, not reproducible, and plausible-looking rows that break rules are exactly what a schema test cannot tolerate.

Why

Deriving every table from one simulated history makes the data consistent, which is what makes the 2020 failures meaningful: when the replay refuses a wing scan, it is because the 2020 key cannot hold a real second visit, not because the data was malformed. A single seed makes the artefacts byte-identical on every rebuild, which the unit tests and the CI's Python check (scripts/verify_db.py, a third SQLite engine) rely on.

What happened

• The build inserts about 66,000 rows into the refined schema with foreign keys and all triggers active, and the integrity and foreign-key checks pass. Rebuilding gives byte-identical files.
• The data is small: about 2 visiting parties a day, against the real Louvre's tens of thousands of visitors. That was a deliberate choice to keep the database at 3.4 MB for the browser, but it means the index timings on /indexes describe a small database. Full scans that take a fraction of a millisecond here would take far longer at real volume, so the measured speed-ups understate what indexes do at scale.
• Every distribution (transport by entrance, guide hire rates, booking show-up rates) is my own assumption. The answers to the business questions describe the simulation, not the museum, and the site says so wherever it shows them.
• The generator was written at the same time as the refined schema, so it encodes that schema's view of the world (DR-001). The 2020 replay is still a fair test of the 2020 design; the refined schema's clean result is partly by construction.

What I'd change

• Add a scale parameter and re-run the index experiments at 10 and 100 times the volume, so the timings say something about growth.
• Calibrate the yearly volume to the Louvre's published attendance (scaled down by a stated factor) instead of a guess.
• Add property-based tests that check the brief's rules on the generated data directly, independently of either schema.