Food Delivery ER diagram and database schema

What this schema models

A complete food delivery app database design for a three-sided marketplace of customers, restaurants and couriers: 29 tables, 22 enums and 49 relationships covering menus with dayparts and modifier groups, restaurants, opening hours and delivery zones with fee schedules, customers and saved addresses, orders with snapshotted lines, promotions and ratings, courier offers, deliveries and tracking events, courier shifts, and payments, refunds and payouts to restaurants and couriers. THE FOUR DESIGN RULES 1. Order lines are snapshots. Order items copy the item name and price, and their modifiers copy the group name, option name and price delta, so editing or deleting a menu item never changes a past order, kitchen ticket or receipt; the menu foreign keys remain for reporting only. Fees, the commission rate and the delivery address are copied onto the order the same way. 2. The order is not the delivery. The order belongs to the customer and the restaurant; a delivery is a courier assignment created from an accepted delivery offer. A courier who drops the job leaves an unassigned row and a new delivery is created, with a partial unique index allowing only one live delivery per order. Pickup orders never get a delivery at all. 3. Three sides, three ledgers. The customer side is payments and refunds; the restaurant side is ledger entries (food sales, minus commission, minus restaurant-funded promotions and refund chargebacks); the courier side is earnings by component. Each sweeps into its own payouts, and delivery fees and tips never touch the restaurant ledger. 4. Modifier groups are reusable and carry their own rules. "Choose a size" or "Add toppings" is defined once with minimum, maximum and free selections, then attached to many items through a join table. Items are placed into menu categories the same way, so one item can sit on the breakfast and the all-day menu at one price. DETAILS WORTH STEALING - Refunds record the party at fault (restaurant, courier, platform or customer), which decides who absorbs the cost; a restaurant-fault refund is charged back on its ledger. - Promotions record who funds them (platform, restaurant or shared, with the restaurant share), because that decides whose ledger the discount hits. - Ratings are split by subject, so a cold meal from a slow courier is not held against the kitchen. - Orders carry a timestamp for each milestone (placed, accepted, ready, completed, cancelled), so prep time and total wait are plain subtractions. - Opening hours allow split lunch and dinner services and shifts that run past midnight, and menus have their own time-of-day windows. - Alcohol items require an ID check at handoff: only age-verified couriers can take them, and the check is logged as a tracking event. WHAT YOU GET Clean DBML that imports directly into DB Planner, and a laid-out board with zones color-coded and framed: menus, restaurants and delivery zones, customers, orders, delivery, couriers, and money. Every table carries a note. Postgres-flavoured types, with PostGIS geography columns for locations and zones; the rest ports cleanly to MySQL or SQL Server. Useful for developers building a food delivery or online ordering marketplace, and for students who need a realistic food delivery app ER diagram for a DBMS project. This is a schema design reference; tax, tipping and courier classification rules vary by market and belong in your own business logic.

Tables (29)

Relationships (49)

• courier_earnings.courier_id references couriers.id many to one
• courier_earnings.courier_payout_id references courier_payouts.id many to one
• courier_earnings.delivery_id references deliveries.id many to one
• courier_payouts.courier_id references couriers.id many to one
• courier_shifts.courier_id references couriers.id many to one
• courier_shifts.delivery_zone_id references delivery_zones.id many to one
• couriers.home_zone_id references delivery_zones.id many to one
• customer_addresses.customer_id references customers.id many to one
• deliveries.courier_id references couriers.id many to one
• deliveries.courier_shift_id references courier_shifts.id many to one
• deliveries.order_id references orders.id many to one
• delivery_events.delivery_id references deliveries.id many to one
• delivery_offers.courier_id references couriers.id many to one
• delivery_offers.id references deliveries.delivery_offer_id one to one
• delivery_offers.order_id references orders.id many to one
• menu_categories.menu_id references menus.id many to one
• menu_category_items.menu_category_id references menu_categories.id many to one
• menu_category_items.menu_item_id references menu_items.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.restaurant_id references restaurants.id many to one
• menus.restaurant_id references restaurants.id many to one
• modifier_groups.restaurant_id references restaurants.id many to one
• modifier_options.modifier_group_id references modifier_groups.id many to one
• order_item_modifiers.modifier_option_id references modifier_options.id many to one
• order_item_modifiers.order_item_id references order_items.id many to one
• order_items.menu_item_id references menu_items.id many to one
• order_items.order_id references orders.id many to one
• orders.customer_address_id references customer_addresses.id many to one
• orders.customer_id references customers.id many to one
• orders.promotion_id references promotions.id many to one
• orders.restaurant_id references restaurants.id many to one
• payment_methods.customer_id references customers.id many to one
• payments.order_id references orders.id many to one
• payments.parent_payment_id references payments.id many to one
• payments.payment_method_id references payment_methods.id many to one
• promotions.restaurant_id references restaurants.id many to one
• ratings.courier_id references couriers.id many to one
• ratings.order_id references orders.id many to one
• refunds.order_id references orders.id many to one
• refunds.order_item_id references order_items.id many to one
• refunds.payment_id references payments.id many to one
• restaurant_hours.restaurant_id references restaurants.id many to one
• restaurant_ledger_entries.order_id references orders.id many to one
• restaurant_ledger_entries.refund_id references refunds.id many to one
• restaurant_ledger_entries.restaurant_id references restaurants.id many to one
• restaurant_ledger_entries.restaurant_payout_id references restaurant_payouts.id many to one
• restaurant_payouts.restaurant_id references restaurants.id many to one
• restaurants.delivery_zone_id references delivery_zones.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 Food Delivery database schema?

29 tables and 49 relationships, across 269 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.