A Restaurant Delivery Data Model

Hungry but you don’t want to cook? Call up a restaurant, order your favorite meal, and read about a data model that can organize the whole process.

Despite an abundance of “time-saving” technology, we seem to have less time to fulfill basic needs – such as eating. If we want to eat something but we don’t have the time (or the skills) to cook it ourselves, we can order food from a restaurant (i.e. a takeaway or takeout), which will bring our meals right to our doors. Of course, we have to pay for this convenience, so we expect the food to be good and hot!

Obviously, any restaurant is motivated to keep its customers satisfied. You might be surprised to learn how much work goes into running a takeaway. Most use some type of tracking system to manage orders and deliveries. Let’s look at the data model underneath one such system. Grab yourself a snack, sit back, and enjoy the article.

What Should We Know About the Restaurant Business?

Making food and delivering it to customers is not easy. First of all, you need to have talent and knowledge to prepare delicious meals. You also need to be organized: everything needs to function perfectly if these meals are going to be delivered on time and to the right place!

Before we start delivering any meals, we need to know:

• Who ordered the meal
• Where and when the meal should be delivered
• What dishes are included in the order
• What ingredients we need to fulfill the order
• If the order has already been paid for

We also need to track delivery statuses and record customer feedback about their meal and/or our delivery process. Plus, maybe we want to know which meals are the most (or least) popular. And we should keep some financial information, too, for reporting and analysis purposes.

Let’s assume that we have an app that our customers can use to place orders for delivery. It allows them to pick menu items, pay for them, and specify a delivery time and address. What might the data model underneath such an app look like?

The Data Model

You can open this model in your browser by clicking the Edit model in your browser button.

The data model consists of three subject areas:

• Restaurants & customers
• Menus
• Orders

We’ll present each subject area in the order it is listed.

Restaurants and Customers

The Restaurants & customers subject area contains three tables that store details about our restaurants (there can be more than one), the cities where we operate, and our customers.

Both customers and restaurants are located in cities (or towns, villages, etc). Therefore, we need a city dictionary. It contains only two attributes, city_name and zip_code. If we operate in more than one country, we would also need a country dictionary that would be related to this table, but we won’t go into that here.

Next, we need a list of all the restaurants we operate. We’ll use the restaurant table for that. To keep things simple, we’ll only store each restaurant’s address and a reference to the city where it is located.

The last table in this subject area is the customer table. This is where we’ll store a list of all our registered delivery customers. We’ll use data from this table to link customers to their orders later in the model. Of course, customers don’t have to be registered in our model to place an order, but we still need this list. We could offer discounts to registered customers as a loyalty program. Or perhaps we would use this data to contact customers with special offers. For each registered customer, we’ll store:

• customer_name – The customer’s full name.
• city_id – References the city where the customer lives.
• address – The customer’s address.
• contact_phone – The customer’s phone number.
• email – The email address the customer used during the registration process.
• confirmation_code – A confirmation code used during the registration process.
• password – The password selected by the customer for this app.
• time_joined – A timestamp of when the customer joined our application.

Menus

This subject area contains information about our restaurants’ menus. For now, let’s assume that all our restaurants use the same menu.

The first table is the category dictionary. It contains only one UNIQUE attribute, category_name. This field will probably hold the usual menu categories, such as “drinks”, “starters”, “salads”, “sandwiches”, “pizza”, etc.

Next, we have the menu_item table. It lists all items we have (or had) on any of our menus. For each item, we’ll store:

• item_name – A name for that item, e.g. “chicken sandwich”.
• category_id – References the category that the item belongs to, e.g. “sandwiches”.
• description – A description of that item. This should be the same as on the printed menu.
• ingredients – The ingredients used to produce that item and their quantities. This field could actually store a recipe.
• price – The current price for one item (e.g. one chicken sandwich).
• active – If the item is offered on the current menu.

If we want to store menus in multiple languages, we should use an approach like the one presented in this article.

Most restaurants have special, limited-time offers. They may also have some offers for an unlimited amount of time. We’ll use the offer table for these. For each one, we’ll have:

• date_active_from and date_active_to – Together, these define when this offer is active. If an offer has an unlimited duration or if it’s based on hours rather than days, these two attributes will contain NULL values. An example of this type of offer is “During the month of March, buy one curry and get one 50% off”.
• time_active_from and time_active_to – Defines the time of day an offer is valid – e.g. “Get a free coffee from 6-9 a.m. every day”.
• offer_price – The price for that offer.

All menu items included in offers are stored in the in_offer table. This table contains the UNIQUE pair of offer_id – menu_item_id.

If our restaurants have different menus, we need to create a separate menu for each restaurant. We’d then need to relate menus and offers with restaurants using foreign keys. This would allow us to change menus and offers for any restaurant without impacting the others. This wouldn’t just complicate the database; the business model would also get more complex. This is why most restaurant chains stick with just one menu and why I decided not to use this method in this model.

Orders

The last subject area in our model is the Orders subject area. This is where we’ll have everything needed to store orders and their statuses.

The central table here is the placed_order table. It’s best not to use just “order” as the name of this table: “order” is an SQL keyword. Try to avoid using keywords as names for tables and columns; otherwise, you may get errors when writing queries. For each order, we’ll record:

• restaurant_id – The ID of the restaurant related to that order.
• order_time – A timestamp of when the order was placed.
• estimated_delivery_time – A timestamp of the planned delivery of this order.
• food_ready – A timestamp denoting when the order items were prepared. This will contain a NULL value until the food is prepared. We could use this attribute to calculate the time difference between the moment order was placed and when the food was prepared. We could also use it to find how much time elapsed between when the food was ready and when it was delivered. This information can be very helpful in terms of increasing the staff’s efficiency.
• actual_delivery_time – A timestamp of when this order was actually delivered. It will be NULL until the food is delivered to the customer.
• delivery_address – The address where the order should be delivered.
• customer_id – The ID of the customer who placed that order. This attribute could contain a NULL value if the order was placed by someone who is not a registered app user.
• price – The price for all items included in that order.
• discount – The amount of discount (e.g. coupon or loyalty discount) applied to the price, if any.
• final_price – The order price minus the discount.
• comment – Additional comments inserted by the customer when the order was placed. This could be additional delivery instructions or anything else the customer finds important.
• ts – A timestamp of when this record was inserted in the table.

The in_order table lists all items or special offers that are included in an order. For each record in this table, we’ll store:

• placed_order_id – The ID of the related order.
• offer_id – References the offer table, but only when one or more offers are included in this order. In that case, the menu_item_id attribute will be NULL.
• menu_item_id – References the menu_item table, but only if this record is related to a menu item and not an offer.
• quantity – How many offers or menu items are included in this order.
• item_price – The price of a single offer or menu item.
• price – The total price for this line, expressed as item_price * quantity.
• comment – Any comments inserted by the customer that relate specifically to that order item, e.g. “Please cut pizza into 8 slices”.

The comment table lets us support the insertion of multiple comments related to orders. For each comment, we’ll store the ID of the related order and the ID of the customer. We’ll also store a timestamp of when this comment was entered. We’ll also mark whether this comment was a complaint or a compliment; only one of these two can be set at one time. If none are set, then we’ll treat this comment as neutral.

The last two tables in our model are related to statuses we’ll assign to orders. The status_catalog table contains a list of all possible UNIQUE status_name attributes that we could assign to orders. The order_status table contains all statuses that are assigned to orders. For each record in this table, we’ll store foreign keys related to order and status and the timestamp when this status was assigned.

What Do You Think About The Restaurant Delivery Data Model?

Today we have discussed a data model that could be used to organize, manage, and store restaurant delivery orders. We can track the status of each order and some of the financial details. I have a few ideas on how we could make this model more robust, but I would be glad to hear your opinion. Please share it in the comments section!