Schema explorer

Three views of one museum

The physical model as submitted in 2020, the conceptual Chen diagram it came from, and the refined schema that now runs the demo database. Hover over or tap a relationship label to read its cardinality in words and the assumption I wrote to justify it; select a table for its columns, keys and SQL. “Side by side” puts the original 2020 export next to the interactive version.

As designed (2020) against refined (2026)

What changed, and why

Every table on the 2020 diagram and what became of it. Each change links to the finding in the design review that justifies it, and the finding to its evidence.

tables: 1619
relationships: 1823
columns: 6984
views: 02

3 kept6 restructured4 split2 merged1 replaced by a view

Ticketticket
restructuredThe barcode keeps its own unique column beside a numeric key, the ticket points at a whole order, and the transport question moves to the first entry scan.
Why: Foreign keys point at part of a composite key; Re-entry through the same gate cannot be recorded; Where a sale happened is only recorded for card payments
Invoicespurchase_orderorder_line
splitOne invoice row per product became an order with order lines, so a sale can hold several products, records when and where it happened, and every foreign key points at a whole key.
Why: Foreign keys point at part of a composite key; No purchase records when it happened; A foreign key joins text to a number
Productproduct
keptSame role. Prices are integer euro cents instead of FLOAT, and each product has a fixed code.
Why: Spaces and typos in names
Payment Methodspayment
restructuredOne row per payment with a single method column, instead of a primary key built from two optional columns.
Why: Payment Methods has optional columns in its primary key; A foreign key joins text to a number
EFTPOScard_paymentfinancial_institution
splitCard details become a subtype of payment sharing its key; banks move to their own table; card digits are stored as text so leading zeros survive.
Why: The masked card number is stored as an integer; Where a sale happened is only recorded for card payments; No purchase records when it happened
Cashcash_payment
keptStill the first name, city and country of a cash payer, now a subtype of payment sharing its key.
Why: Payment Methods has optional columns in its primary key
Entranceentry_scanentrance
splitEvery entrance scan gets its own row and key, so re-entry through the same gate fits; the five gate names become a lookup table.
Why: Re-entry through the same gate cannot be recorded
Wingswing_scanwing
splitKeyed by the wing's name, the 2020 table could hold three rows in total. Each wing scan now has its own row, ticket and time; the wings become a lookup table.
Why: Wings can store only one visit per wing, ever
Hired Audio Guideaudio_guide_hirelanguage
restructuredEach hire has its own key and records the order that paid for it; the 13 languages become a lookup table instead of an ENUM.
Why: Audio guides still in service cannot be registered; Spaces and typos in names
Audio Guide Deviceaudio_guide_device
restructuredThe retirement date is optional, so a device still in service can be registered (and its hires stored).
Why: Audio guides still in service cannot be registered
Special Exhibitionexhibition
keptSame role, with its run dates and the places per slot.
Why: Time slots have no date and no exhibition
Time Slotexhibition_slot
restructuredA slot is now a dated 15-minute slot of one exhibition, with its capacity, instead of a time of day shared by every exhibition.
Why: Time slots have no date and no exhibition
Exhibition Bookingexhibition_booking
mergedMerged with Booking Ticket: one row per ticket per slot, with its own key, and a trigger that stops overbooking.
Why: Foreign keys point at part of a composite key; Three overlapping ways to link tickets and bookings, and a stored count; Time slots have no date and no exhibition
Booking Ticketexhibition_booking
mergedIts only job, linking tickets to bookings, is now a column of exhibition_booking.
Why: Foreign keys point at part of a composite key; Three overlapping ways to link tickets and bookings, and a stored count
Hall Napoleonhall_napoleon_scan
restructuredAn admission belongs to one booking (at most one per booking), so attendance can be traced to an exhibition.
Why: Three overlapping ways to link tickets and bookings, and a stored count; Time slots have no date and no exhibition
Actual Number of Visitorslot_availability (view)
replaced by a viewA stored count that could drift from the rows it counts becomes a view computed from bookings and admissions.
Why: Three overlapping ways to link tickets and bookings, and a stored count
newmuseum_visit (view)
One row per visit (a ticket's first entry), for counting visitors.

Submitted with the model

The 13 assumptions

Quoted exactly as written in 2020, including the grammar. They explain cardinalities more than they resolve ambiguities in the brief; several (5, 9, 12) describe rules the physical model does not enforce.

1
In the museum, visitors need to buy tickets, besides they can hire audio devices and purchase official applications. Purchasing records in invoices entity to avoid duplication and redundancy.
About: Invoices
2
If there is no product, there is no need for invoices. Hence Invoices is a weak entity, and each invoice can only obtain one product. So the relationship between them is mandatory one to one.
About: Invoices, Product
3
The relationship between ticket and invoices is many to one relationship due to there is no purchase quantity limit; however, each ticket can only have one invoice record.
About: Ticket, Invoices
4
A valid ticket can enter any of five entrances, but each valid ticket can only pass through one entry. Therefore, the relationship between entrance and ticket is a mandatory one to many relationship.
About: Entrance, Ticket
5
A visitor and visit wings are many times. However, each valid ticket can only visit a wing at a time. Hence, the relation between wings and ticket is one many to one relationship.
About: Wings, Ticket
6
A visitor can hire an audio guide at each wing, and a visitor can hire an audio guide as many times as they want. So the relationship between hired audio guide and ticket is many to one relationship.
About: Hired Audio Guide, Ticket
7
The hired audio guide is a weak entity towards the audio guide devices. For each hiring request, there is only one audio device deploy at a time. Thus, the relationship of Hired Audio Guide and Audio Guide Devices is a mandatory one to one relationship.
About: Hired Audio Guide, Audio Guide Device
8
As Special Exhibitions, each exhibition has multiple booking records. Exhibition Booking is a weak entity for the Special Exhibition. The relationship between them is a mandatory one to many relationship.
About: Exhibition Booking, Special Exhibition
9
Each ticket can book only one exhibition time slot. So the relationship between ticket and exhibition booking is one to one relationship.
About: Ticket, Exhibition Booking, Booking Ticket
10
Fixed Time Slot.
About: Time Slot
11
Time Slot is a weak entity for Exhibition Booking. Time Slot issued by Exhibition Booking, each exhibition booking log can have many available time slots. However, each time slot can only assign to one booking log. Hence the relationship between Time slot and Exhibition Booking is mandatory many to one relationship.
About: Time Slot, Exhibition Booking
12
Hall Napoleon records actual visiting visitors. Since each ticket can only book a time slot, the visitor can only visit the exhibition once. So, the relationship between Hall Napoleon and Ticket is one to many relationship.
About: Hall Napoleon, Ticket, Actual Number of Visitor
13
Hall Napoleon also records the visiting time slot. The relationship between them is one to many relationship.
About: Hall Napoleon, Time Slot

From the .mwb file

How the model was built

Workbench stores a created and changed time for every table, so the file records its own history.

2020-03-31 16:20
Model created in MySQL Workbench
2020-03-31 16:21
First draft: 11 log-style tables (Museum_Entering_Log, Gate, Transportation, purchase logs). None made it into the final diagram.
2020-04-08 19:16
Redesign: the 16 submitted tables are created between 19:16 and 20:27, starting with Ticket and Invoices.
2020-04-09 17:22
Last save of the final model, after the brief's due date of 3 April 2020. That may reflect an extension or a later re-export; the files cannot confirm which.

The 11 draft tables left in the file

Museum_Entering_Log (7 columns)
Gate (2 columns)
Transportation (2 columns)
Ticket_Purchase_Log (3 columns)
Purchase_Method (3 columns)
Purchase_Option (2 columns)
EFTOP (2 columns)
Cash_Log (0 columns)
EFTOPS_Log (11 columns)
Cash_Log (8 columns)
Purchase Option (2 columns)