A complete food delivery database schema designed for on-demand food ordering platforms. Includes restaurant management with menu items, customer profiles, order processing with line items and status tracking, and delivery driver assignment. Suitable for building apps like Uber Eats, DoorDash, or custom food delivery solutions.
An order points at three parties at once — customer, restaurant and driver — and the driver is nullable. That nullability is the whole dispatch model: an order exists and is being cooked before anyone has been assigned to carry it, and assignment is an update to driver_id rather than a row in a queue table.
order_items copies unit_price from the menu item instead of reading it later. Restaurant prices move constantly, and a receipt that recalculates from today's menu would quietly change what a customer paid last Tuesday. The menu_item_id stays so the dish can still be identified; the number beside it is what was actually charged.
The delivery address is written twice on purpose. customers.delivery_address is the saved default that fills the form, and orders.delivery_address is where this particular order went. Without the second one, moving house would rewrite the destination of every order already delivered.
A proper dispatch system records offers, rejections and reassignments, and that is a table of its own. A single driver_id says only who has it now. For a schema this size that is the right trade — assignment is one update and the live map is one query — but it means a rejected offer leaves no trace, and the question of how many drivers turned this order down has no answer here.
menu_items.price is the current price; order_items.unit_price is the historical one. Joining to the menu for a receipt is simpler and wrong — a price change or a removed dish rewrites or breaks every past order. The duplication is the cost of an order staying true to itself after the menu moves on.
Both carry a rating column with no reviews table behind it. Listing restaurants by rating is the most common read in the product and it needs a sortable number on the row. The limitation is plain: there is nowhere to store who rated, what they wrote, or how to recompute the average — adding reviews later means this column becomes a cache rather than the record.
status answers where the order is now; ordered_at and delivered_at answer how long it took. A full state-transition log would answer when it was picked up and how long it sat at the restaurant, which this schema cannot. It is the deliberate floor — enough to run the product, with the event log as the known next addition.