Restaurant POS data model
Normalized (3NF) restaurant POS model. Location to menu to menu-item with modifiers defines the offer; orders against tables and servers carry order-items (with chosen modifiers) that fire kitchen tickets and settle via payments, while recipes tie menu items to ingredient inventory. Staff PII and card data are classified and masked.
Restaurant point-of-sale and back-of-house operations: locations and their menus, items, and modifiers; dine-in tables and staff; the order/order-item/kitchen-ticket chain with payments; and the inventory side of ingredients and recipes that deplete stock per sold item — modeled as a normalized OLTP POS schema with staff and card data masked. It is a normalized (3NF) relational model that mirrors the source system, suited to operational databases and to landing source data faithfully.
- 13
- tables
- 115
- columns
- 17
- relationships
- 5
- PII columns
Opening the template creates a project with the ERD, platform-native DDL, a dbt project and documentation already generated (recommended platform: PostgreSQL). Edit it in plain English or on the canvas, then ship it to any supported warehouse.
Subject areas
Design decisions
Order / order-item / modifier hierarchy
A guest check is a header with line items, each of which may carry multiple modifier choices; modeling order, order_item, and a modifier junction keeps customizations normalized and priceable.
Recipe as a menu-item-to-ingredient junction
Each menu item consumes many ingredients and each ingredient feeds many items, so a recipe junction captures the M:N bill of materials that drives inventory depletion.
Kitchen ticket separate from the order
Sending a check to the kitchen produces one or more KDS tickets by station with their own lifecycle, distinct from the front-of-house order and payment flow.
Normalized (3NF) rather than dimensional
POS handling is high-volume transactional with open checks, voids, and live inventory updates where relational integrity is paramount.
Tables in the Restaurant POS model
Every table with its grain, columns, types and keys. PK marks a primary key, BK a business key and PII a column classified as personal data, which gets a masking strategy by default.
Tables (13)
location
Grain: one row per restaurant location
Restaurant site.
| Column | Type | Keys |
|---|---|---|
| location_key | bigint | PK |
| location_code | varchar | BK |
| location_name | varchar | |
| address | varchar | |
| city | varchar | |
| country | varchar | |
| timezone | varchar | |
| seating_capacity | integer | |
| currency | varchar | |
| opened_date | date | |
| is_active | boolean |
menu
Grain: one row per menu at a location
Day-part / seasonal menu.
| Column | Type | Keys |
|---|---|---|
| menu_key | bigint | PK |
| location_key | bigint | |
| menu_code | varchar | BK |
| menu_name | varchar | |
| menu_type | varchar | |
| active_from | date | |
| active_to | date | |
| is_active | boolean |
menu_item
Grain: one row per sellable menu item
Sellable item.
| Column | Type | Keys |
|---|---|---|
| menu_item_key | bigint | PK |
| menu_key | bigint | |
| item_code | varchar | BK |
| item_name | varchar | |
| description | varchar | |
| category | varchar | |
| price | decimal | |
| tax_rate | decimal | |
| calories | integer | |
| is_alcohol | boolean | |
| prep_minutes | smallint | |
| is_available | boolean |
modifier
Grain: one row per item modifier option
Item customization option.
| Column | Type | Keys |
|---|---|---|
| modifier_key | bigint | PK |
| menu_item_key | bigint | |
| modifier_name | varchar | |
| modifier_group | varchar | |
| price_delta | decimal | |
| is_default | boolean |
dining_table
Grain: one row per physical table
Dine-in table.
| Column | Type | Keys |
|---|---|---|
| dining_table_key | bigint | PK |
| location_key | bigint | |
| table_number | varchar | BK |
| section | varchar | |
| seats | smallint | |
| status | varchar |
staff
Grain: one row per staff member
Employee record.
| Column | Type | Keys |
|---|---|---|
| staff_key | bigint | PK |
| location_key | bigint | |
| staff_number | varchar | BK |
| first_name | varchar | PII |
| last_name | varchar | PII |
| role | varchar | |
| varchar | PII | |
| phone | varchar | PII |
| hire_date | date | |
| hourly_rate | decimal | |
| is_active | boolean |
order
Grain: one row per order (check)
Guest check header.
| Column | Type | Keys |
|---|---|---|
| order_key | bigint | PK |
| location_key | bigint | |
| dining_table_key | bigint | |
| staff_key | bigint | |
| order_number | varchar | BK |
| order_type | varchar | |
| guest_count | smallint | |
| subtotal_amount | decimal | |
| tax_amount | decimal | |
| tip_amount | decimal | |
| total_amount | decimal | |
| status | varchar | |
| opened_at | timestamp | |
| closed_at | timestamp |
order_item
Grain: one row per line item on an order
Check line.
| Column | Type | Keys |
|---|---|---|
| order_item_key | bigint | PK |
| order_key | bigint | |
| menu_item_key | bigint | |
| line_number | integer | |
| quantity | decimal | |
| unit_price | decimal | |
| amount | decimal | |
| seat_number | smallint | |
| special_instructions | varchar | |
| status | varchar |
order_item_modifier
Grain: one row per modifier applied to a line
Junction: modifiers chosen on an order line.
| Column | Type | Keys |
|---|---|---|
| order_item_modifier_key | bigint | PK |
| order_item_key | bigint | |
| modifier_key | bigint | |
| quantity | smallint | |
| price_delta | decimal |
kitchen_ticket
Grain: one row per kitchen ticket (KDS)
Kitchen display ticket.
| Column | Type | Keys |
|---|---|---|
| kitchen_ticket_key | bigint | PK |
| order_key | bigint | |
| ticket_number | varchar | BK |
| station | varchar | |
| fired_at | timestamp | |
| completed_at | timestamp | |
| status | varchar |
payment
Grain: one row per payment against an order
Tender record.
| Column | Type | Keys |
|---|---|---|
| payment_key | bigint | PK |
| order_key | bigint | |
| payment_number | varchar | BK |
| payment_method | varchar | |
| card_last_four | varchar | PII |
| amount | decimal | |
| tip_amount | decimal | |
| status | varchar | |
| transaction_reference | varchar | |
| paid_at | timestamp |
ingredient
Grain: one row per inventory ingredient
Stock item.
| Column | Type | Keys |
|---|---|---|
| ingredient_key | bigint | PK |
| location_key | bigint | |
| ingredient_code | varchar | BK |
| ingredient_name | varchar | |
| unit_of_measure | varchar | |
| unit_cost | decimal | |
| quantity_on_hand | decimal | |
| reorder_level | decimal | |
| is_allergen | boolean |
recipe
Grain: one row per ingredient used by a menu item
Junction: menu-item bill of materials.
| Column | Type | Keys |
|---|---|---|
| recipe_key | bigint | PK |
| menu_item_key | bigint | |
| ingredient_key | bigint | |
| quantity | decimal | |
| unit_of_measure | varchar | |
| is_optional | boolean |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- menu.location_key → location (many-to-one)
- menu_item.menu_key → menu (many-to-one)
- modifier.menu_item_key → menu_item (many-to-one)
- dining_table.location_key → location (many-to-one)
- staff.location_key → location (many-to-one)
- order.location_key → location (many-to-one)
- order.dining_table_key → dining_table (many-to-one)
- order.staff_key → staff (many-to-one)
- order_item.order_key → order (many-to-one)
- order_item.menu_item_key → menu_item (many-to-one)
- order_item_modifier.order_item_key → order_item (many-to-one)
- order_item_modifier.modifier_key → modifier (many-to-one)
- kitchen_ticket.order_key → order (many-to-one)
- payment.order_key → order (many-to-one)
- ingredient.location_key → location (many-to-one)
- recipe.menu_item_key → menu_item (many-to-one)
- recipe.ingredient_key → ingredient (many-to-one)
Related templates
All templates →From requirements to AI-ready context, in one platform.
Describe your business in a conversation, start from a template or connect your own database. Your first model is ready in minutes.