Restaurant Ordering and POS ER diagram and database schema

What this schema models

A complete restaurant point-of-sale database design, built the way a real front of house and kitchen run a shift: 29 tables, 14 enums and 60 relationships covering locations and the floor plan, a menu with dayparts, daypart pricing and modifier groups, recipes and plate cost, staff, role permissions and the time clock, checks by order type with seats and courses, voids, comps and discounts, kitchen tickets routed to prep stations, split payments and tips, cash drawers and the end-of-day close. THE FOUR DESIGN RULES 1. Nothing is deleted. Voids, comps and discounts are append-only adjustment rows with a reason picked from a fixed list, the employee who asked and the manager who approved. A check line that reached the kitchen is never deleted: a void marks it voided, and a void after firing is flagged as food waste. 2. Check lines are snapshots with a seat and a course. The name and the price actually charged (including a happy-hour daypart price and the modifier deltas) are copied at ring-in. The seat number drives serving and split-by-seat; the course decides firing order, and a line stays held until its course is fired. 3. The kitchen sees tickets, not checks. Firing a course creates one ticket per prep station, each with its own fired, started, bumped and recalled times, so ticket time is measured per station. A void after firing reaches the station as a void notice instead of silently vanishing from the screen. 4. Business date, not calendar date. Checks, payments, time punches and drawer sessions all belong to a business day that rolls over at an hour set per location, so the 1:30 am bar tab lands on the right night. The end-of-day close freezes the totals and locks the day. DETAILS WORTH STEALING - Cash drawers use blind counts: the closer counts without seeing the expected amount, over/short is stored per session, and a partial unique index allows one open session per drawer. - Every safe drop, paid-out, paid-in and no-sale opening is a drawer movement, which is exactly what managers audit. - Splitting a check evenly, by seat or by amount is just several payment rows, and card tips are added at tip adjust after the swipe. - Permissions hang off roles through a join table, PINs are stored only as hashes, and every check, void, comp and payment records the employee. - Time clock punches snapshot the wage, record declared cash tips and any manager correction, and only one open punch per employee is allowed. - Recipe lines link ingredients to items and to modifiers such as "Add bacon", which gives plate cost and theoretical usage per day. WHAT YOU GET Clean DBML that imports directly into DB Planner, and a laid-out board with zones color-coded and framed: recipes and food cost, menu, locations and floor plan, checks and adjustments, kitchen display, payments, cash and day close, and staff and permissions. Every table carries a note. Postgres-flavoured types that port cleanly to MySQL or SQL Server. Useful for developers building a restaurant POS, ordering or kitchen display system, and for students who need a realistic restaurant POS ER diagram for a DBMS project. This is a schema design reference; tax, tip and labor rules vary by jurisdiction and belong in your own business logic.

Tables (29)

Relationships (60)

• adjustment_reasons.location_id references locations.id many to one
• business_days.closed_by references employees.id many to one
• business_days.location_id references locations.id many to one
• cash_drawers.location_id references locations.id many to one
• check_adjustments.adjustment_reason_id references adjustment_reasons.id many to one
• check_adjustments.approved_by references employees.id many to one
• check_adjustments.check_id references checks.id many to one
• check_adjustments.check_item_id references check_items.id many to one
• check_adjustments.requested_by references employees.id many to one
• check_item_modifiers.check_item_id references check_items.id many to one
• check_item_modifiers.modifier_option_id references modifier_options.id many to one
• check_items.check_id references checks.id many to one
• check_items.menu_item_id references menu_items.id many to one
• check_items.rung_in_by references employees.id many to one
• checks.business_day_id references business_days.id many to one
• checks.dining_table_id references dining_tables.id many to one
• checks.location_id references locations.id many to one
• checks.server_id references employees.id many to one
• checks.split_from_check_id references checks.id many to one
• dayparts.location_id references locations.id many to one
• dining_areas.location_id references locations.id many to one
• dining_tables.dining_area_id references dining_areas.id many to one
• drawer_movements.approved_by references employees.id many to one
• drawer_movements.drawer_session_id references drawer_sessions.id many to one
• drawer_movements.employee_id references employees.id many to one
• drawer_sessions.business_day_id references business_days.id many to one
• drawer_sessions.cash_drawer_id references cash_drawers.id many to one
• drawer_sessions.employee_id references employees.id many to one
• drawer_sessions.verified_by references employees.id many to one
• employees.location_id references locations.id many to one
• employees.role_id references roles.id many to one
• ingredients.location_id references locations.id many to one
• item_daypart_prices.daypart_id references dayparts.id many to one
• item_daypart_prices.menu_item_id references menu_items.id many to one
• kitchen_ticket_items.check_item_id references check_items.id many to one
• kitchen_ticket_items.kitchen_ticket_id references kitchen_tickets.id many to one
• kitchen_tickets.check_id references checks.id many to one
• kitchen_tickets.fired_by references employees.id many to one
• kitchen_tickets.prep_station_id references prep_stations.id many to one
• menu_categories.daypart_id references dayparts.id many to one
• menu_categories.location_id references locations.id many to one
• menu_item_modifier_groups.menu_item_id references menu_items.id many to one
• menu_item_modifier_groups.modifier_group_id references modifier_groups.id many to one
• menu_items.menu_category_id references menu_categories.id many to one
• menu_items.prep_station_id references prep_stations.id many to one
• modifier_groups.location_id references locations.id many to one
• modifier_options.modifier_group_id references modifier_groups.id many to one
• payments.business_day_id references business_days.id many to one
• payments.check_id references checks.id many to one
• payments.drawer_session_id references drawer_sessions.id many to one
• payments.employee_id references employees.id many to one
• prep_stations.location_id references locations.id many to one
• recipe_lines.ingredient_id references ingredients.id many to one
• recipe_lines.menu_item_id references menu_items.id many to one
• recipe_lines.modifier_option_id references modifier_options.id many to one
• role_permissions.role_id references roles.id many to one
• time_clock_entries.business_day_id references business_days.id many to one
• time_clock_entries.edited_by references employees.id many to one
• time_clock_entries.employee_id references employees.id many to one
• time_clock_entries.role_id references roles.id many to one

How to use this template

1. Open the ER diagram to see the tables and how they relate.
2. Copy it into your workspace. You get your own editable copy, in DBML.
3. Export it as PostgreSQL, MySQL, or SQL Server DDL, or as C# models.

The copy opens in DB Planner’s ER diagram tool, with the schema as text in the DBML editor beside it. Have a database already? Turn its SQL into an ER diagram instead.

Frequently asked questions

What is in the Restaurant Ordering and POS database schema?

29 tables and 60 relationships, across 220 columns. The table names and the full relationship list are shown on this page.

Can I export it to PostgreSQL or MySQL?

Yes. Every DB Planner template exports as DBML, as PostgreSQL, MySQL, or SQL Server DDL, and as C# models.

Is this template free?

Yes. You can open it in the browser without an account, and copy it into your own workspace with a free DB Planner account.

Can I change it after copying it?

Yes. A copy is yours: you can rename tables, add columns, and export it. Changes the author makes later do not affect your copy.