MenuDatabase

The model

Ten tables, one fact in one place

Four reference/entity tables (categories, menu items, ingredients, plus customers, staff and dining tables), two many-to-many junction tables, and the transactional core — orders, order lines and payments. A dish stores its category_id, never the category's name; an order stores who, where and when by id, never copies of names.

menu_items ⇄ ingredients and orders ⇄ menu_items are both many-to-many, each resolved by a junction table that also carries the data that belongs to the pairing (an ingredient quantity; a line quantity and the price at time of sale).

Normalisation

Why it's split this way

• 1NF — no repeating groups. A menu item never holds a comma-list of ingredients; the link lives one row per pair in item_ingredients.
• 2NF — full-key dependency. Junction columns like quantity and unit_price depend on the whole pairing, not one side of it.
• 3NF — no transitive dependencies. menu_items stores category_id, not the category name; orders stores ids, not copied names.
• Deliberate, non-redundant history. order_items.unit_price is the price at time of sale — a fact about the line, independent of today's menu price.
• Constraints are business rules. CHECKs forbid negative prices and unknown payment methods; UNIQUE forbids duplicate item lines on one order.

A query, end to end

Revenue by category

Order lines joined up through menu items to their category, cancelled orders excluded. The SQL on the left; its real output on the right.

SELECT c.name AS category,
       ROUND(SUM(oi.quantity * oi.unit_price), 2) AS revenue
FROM   order_items oi
JOIN   orders     o  ON o.order_id = oi.order_id
JOIN   menu_items mi ON mi.item_id = oi.item_id
JOIN   categories c  ON c.category_id = mi.category_id
WHERE  o.status <> 'cancelled'
GROUP  BY c.category_id
ORDER  BY revenue DESC;

Total = 137.50, which equals the sum of all recorded payments — the schema reconciles.

The query library

Real answers from the seed data

Eleven queries answer real questions. A few, run by demo.py:

Also: busiest hours (13:00, 3 orders) and tables (T1, 2 orders), average order value (27.50 over 5 orders), customer order history, items sharing ingredients (Bruschetta & Margherita share Tomato + Basil), and an order-vs-payment reconciliation view.

Verification

Built, seeded, tested

A pytest suite checks the schema creates cleanly with foreign keys enforced, that orphan inserts raise IntegrityError, that CHECK constraints reject a negative price and a bogus payment method, that every query returns its hand-verified result, and that the model stays normalised (no ingredient column on menu_items).

$ python -m pytest -q
......................                                                   [100%]
22 passed in 0.10s