Food delivery apps are an interesting case study for designing a database.
There are multiple types of users, and many features in the app.
In this video, you’ll see a database design for a food delivery app, a list of requirements it
has been based on, and an explanation of the design and how it meets the requirements.
The goal is to help you improve your database design skills.
Let’s get into it.
There are 8 requirements that this design is based on.
We’ll go through them one by one as we look at the design shortly.
In summary, they allow customers to order items
from a restaurant and have them delivered by a delivery driver to their address.
Let’s look at our design.
Here’s our design for the food delivery app database. You can get a link to the image
of this design using the link in the description. I’ve designed this using a tool called LucidChart.
Let’s go through each of our requirements to see how they are met.
Before we do, if you want to have an easy-to-follow checklist for making sure your
database is well-designed, check out my Database Normalisation Checklist in the description below.
It’s a checklist of a range of things you can look for
in your database design to ensure it’s efficient and well-designed.
The first requirement states that “customers can choose a restaurant
and place an order for food from the restaurant”.
How do we do this in the database?
We have a customer table here. It has a primary key of ID, like all of the tables in the database.
We also store a first name and last name.
We could store more information here, but we don’t yet have any requirements to do so.
To choose a restaurant and place an order, they would create a record in the food_order table.
The customer_id is a foreign key here, and is linked back to the customer table.
They would select a restaurant, which is from the restaurant table here.
The restaurant just has a name, and is linked to the food_order table.
Something we could add to the restaurant table is the cuisine of the restaurant,
but we have no requirement for that yet.
In the food_order table, we have the order_datetime column,
which is when the order is placed.
How can they order food? Let’s look at requirement 2.
The second requirement is “each restaurant has different menu items and prices”.
We’ve done this in our diagram by adding the menu_item table next to the restaurant table.
A restaurant can have one or more menu items, each of which has its own name and price.
A menu item could be a pepperoni pizza or a korma curry.
We could expand this to allow users to modify the items in their order,
by adding or removing ingredients to an item. I haven’t added it to this design, but it’s
something that is common in food delivery apps. If you want to do that as an exercise, feel free to.
So we’ve got our menu items. How do we add them to an order?
We have another table here called order_menu_item.
This stores the menu items that are included in each order.
This has a link to the menu item, to specify which item is ordered. We also specify the order ID.
We specify the qty ordered, because the customer may want to order more than one of the same item.
What’s next?
The third requirement is “Customers can specify a delivery time,
either ASAP (when the order is placed) or at a future time”.
This can be implemented in our database by adding a requested_delivery_datetime field
to the food_order table. The app can ask the user if they want it ASAP or to select a time.
It would then store this delivery time in the requested_delivery_datetime field.
We’ve called it requested_delivery_datetime because having a field called delivery_datetime
might imply that it is the time when the delivery was completed.
We want to make it clear that this is the requested time.
The fourth requirement is “Customers can store multiple delivery addresses on their profile”.
We do this by having a table called address. We’ve used the same structure for addresses as
my eCommerce database design video, so if you haven’t seen that video you can watch it here.
We have a range of fields for an address,
and a separate country table to store a list of countries.
The address is related to a restaurant, as a restaurant exists at an address.
It’s done a little differently for customers. We can have multiple addresses for a customer,
so we have a joining table in the middle. This stores an entry for a customer and an address.
This customer_address table is also linked to an order.
It allows an order to be sent to a specific address for a customer.
Requirement number 5 is “Restaurants can accept
the order and update the status of the order as it is made”.
This can be done by having an order_status for an order. We have created a separate table
to store the list of possible values, and the food_order table has a link to this status table.
The app can handle the functionality to allow restaurants, and perhaps delivery drivers,
to change the status. The database will store it in these columns.
We could have statuses for order submitted, order accepted by restaurant, preparing order,
order ready for pickup, delivery in progress, and delivery completed.
We could also have other problem statuses like order cancelled.
Requirement 6 is “Delivery drivers can pick an order to deliver to assign it to themselves”.
We’ve done this by adding a new table for delivery drivers, where we store the first and last name.
We’re not sure we need much more than this at the moment.
We capture the assignment of an order to a driver in the assigned_driver_id column.
The driver could use a feature in the app to select an order and assign it to themselves.
The seventh requirement says “Delivery drivers can pick up an order that is
ready from the restaurant and deliver it to the customer”
This could be done mostly within the app. Perhaps the app could show
all orders that are ready that the driver has assigned to themselves.
Or it could inform the delivery driver that an order status is changed and it is now ready.
We’ve designed for this in our earlier requirements,
so I don’t think we need to make any changes here.
Our final requirement, number 8, is “Customers can rate delivery drivers and restaurants”.
We’ve done this by capturing the driver rating in the cust_driver_rating field on
the food_order, as they may want to specify different ratings for different orders.
We can review the restaurant by storing the value in the cust_restaurant_rating column too.
Using these columns, we could use a 1-5 rating system, or a positive or negative value system,
or some other system. It’s up to the food delivery app.
There are a couple of other features to point out in this design.
There’s a delivery fee column on the food_order table to record what the delivery fee is.
We also have the total_amount column on the food order,
which is a calculated value that the customer is charged for the order.
It includes the prices of the menu items that were ordered and the delivery fee.
It allows a customer to see what their amount is,
even if the restaurant changes their prices in the future.
So that’s how I would design a database for a food delivery app.
I’ve got a link to an image of this diagram in the description below.
Also, if you want to get a copy of my Database Normalisation Checklist,
check out the link in the description below.
If you enjoyed this video, consider subscribing to my channel.
If you have any questions or comments on the design, leave a comment below.
Thanks for watching.