Skip to content
Louvre Ops DB

Decision record DR-001

Keep the 2020 design exactly as submitted and build a separate refined schema beside it

Status:
accepted
Decided:
7 October 2026

Scope: coursework/, web/src/data/mwb-2020.json, web/data/ddl/, /schema, the home page's ERD and diff

Context

The 2020 submission is a Chen diagram, a 16-table MySQL Workbench model (1118472.mwb) and 13 assumptions. When the model is forward-engineered and run, MySQL 26.7 creates 13 of the 16 tables: Payment Methods puts optional columns in its primary key, and Invoices and Booking Ticket are referenced by foreign keys that point at part of a composite key. Replaying five synthetic years into a SQLite port keeps 43,521 of 64,130 rows (68%), and on MySQL with foreign-key checks on only 5,473 (9%). Of the brief's ten business questions, the 2020 design answers 6, 2 partly and 2 not at all. The revival needs a working database for the playground, the questions and the AI features, and it must also show the 2020 work honestly, because the whole point of the site is to demonstrate the ERD as it was designed.

Decision

The 2020 model stays exactly as submitted. It is parsed from the .mwb file (with a SHA-256 check), drawn at its original positions, and forward-engineered to MySQL and to a faithful SQLite port, mistakes included. A separate refined schema (19 tables, 2 views, 8 triggers, 23 foreign keys) runs the demo database. Every difference between the two is listed in a table-by-table diff on the home page and /schema, and every change points at a finding of the design review, which in turn cites its evidence (a MySQL error, the replay, or the brief).

Options considered

  1. Fix the 2020 model in place. The simplest site, but it would silently change graded coursework and erase the evidence of what went wrong.
  2. Run the demo on the 2020 tables. Faithful, but the tables cannot hold the data (68% kept on SQLite, 9% on MySQL) and two questions cannot be asked, so the playground would mostly demonstrate errors.
  3. Keep the original untouched and add a refined schema beside it, with a traceable diff. Chosen.
  4. Redesign from scratch. A cleaner schema, but nothing would connect it to the 2020 work, so it could not show what was learned.

Why

Option 3 is the only one that keeps the coursework faithful and still gives the site a database that works. Showing both side by side (the Workbench export, the interactive redraw, and the refined schema) lets a reader check every claim: the redraw against the image, the review against the MySQL log, the refined schema against the review. Tying each change to a finding stops the refined schema from drifting into a rewrite that cannot be justified.

What happened

  • The interactive diagram reproduces the 2020 layout from the .mwb coordinates; the Workbench export sits beside it in the "side by side" view, so the redraw can be checked against the original by eye.
  • The refined schema answers all ten business questions and stores every row of the synthetic dataset with foreign keys and triggers on. That second result is weaker than it sounds: the data generator was written alongside the refined schema (DR-002), so the data fits it partly by construction. The 2020 replay is the fairer test, because the 2020 tables were fixed long before the data existed.
  • The diff covers all 16 tables: 3 kept, 6 restructured, 4 split, 2 merged into one and 1 replaced by a view (Actual Number of Visitor, a stored count, became slot_availability). Only one refined relation has no 2020 table behind it, the museum_visit convenience view.
  • The refined schema is bigger (19 tables against 16, 23 foreign keys against 18), and it is SQLite-only: STRICT tables and partial indexes would need translating for MySQL, which I have not done.
  • Its indexes are not free. In the index experiments (/indexes), the whole-table route query (business question 5) takes about 1.2 times as long with the index on wing_scan(ticket_id) as without it (speed-up 0.85, 95% interval 0.84 to 0.86, on the build machine), because SQLite walks the index to avoid a sort and pays for a lookup per row.

What I'd change

  • Run the refined schema on MySQL as well, so the comparison with the 2020 model is on the same engine.
  • Test it against data generated independently of it, by someone else or from a published specification, so "it stores everything" is not partly circular.
  • Ask a second reviewer to grade the design review's findings; at the moment the reviewer and the 2020 author are the same person, five years apart.