Database Design for a Food Delivery App (Example & Walkthrough)

[@arbazadam3407]

Hello Ben. I encourage you to create more videos on designing databases for applications. We can easily pick syntax from the documentation but when the need to design the databases arises. Thats where we struggle.

[@DatabaseStar]

Hey! Thanks for the suggestion, that’s a good idea. Are there any domains or systems that you would like to see a database design created for? I’ve got a few on my list but want to see if there are any you’d like to see.

[@chethanb6406]

Quora

[@VaibhavKarbhajan]

Very simple and good design ! The design can be extended to add more things such as currency for instance.

[@DatabaseStar]

Thanks! Yes that’s a good point, it can be.

[@mickeymittal-j1b]

Thank you for the wonderful video! For the task of adding ingredients, I was able to create a list of available ingredients for a specific menu item by setting up an ingredients table. Since the relationship between menu items and ingredients is many-to-many, I also created a junction table that stores the IDs of both the menu items and the ingredients. However, I'm unsure how to store the ingredients that a user selects in the order_menu_item table. Could you please guide me on how to approach this?

[@DatabaseStar]

Glad you like the video! I assume that a user can make multiple ingredient changes for an item, so I think there would be a joining table (for example, named "item_modifications") between order_menu_item and the new joining table for menu_item_ingredients. In this table you could store the changes the user makes to a menu item for each order. I think this would work but not certain. You could create these tables (or add them in a spreadsheet) and test that it works.

[@jayjoshi1366]

Great video and content displayed. Can you specify which database can be used for Food Delivery apps and websites like Doordash or Uber eats, and how can the DB be queried if any customer searches specific restaurant or any food item universally which may go to any restaurant ? Your response will be appreciated.

[@DatabaseStar]

Thanks! You can use any relational database for this concept such as MySQL or Postgres or SQL Server. You would write Select queries to get the data you need from the tables.

[@thuongngo8010]

you are really good teacher , hope u make more video like this

[@DatabaseStar]

Thanks!

[@trishulcurtis1810]

Excellent Video!

[@DatabaseStar]

Thanks!

[@irshadahmed6801]

Great video!!. What about having reviews in a separate table. Because we might have index reviews based on just resturant or delivery driver

[@DatabaseStar]

Yes, you could have reviews in a separate table, if you wanted to review just the restaurant or just the delivery driver.

[@hanhthien2948]

Hello sir, I have a question, why not combine the customer table and delivery_driver table in one table? I see they have the same row, why not just combine and have a column named 'role' in the user table?

[@DatabaseStar]

Good question. Essentially it’s because they are different things. But if they share enough of the same details and you want to have a single profile with different roles then yes that could work.

[@coastalcruise1345]

Hey Ben, very helpful video thanks. Regarding the order_menu_item table, could we use a composite primary key from its two foreign keys: order_id and menu_item_id ?

[@DatabaseStar]

Hey, yes you could use that for the primary key instead.

[@oracle_professor]

Really helpful for my students

[@DatabaseStar]

Glad to hear it!

[@-.-smile]

Amazing videos!

[@DatabaseStar]

Thanks!

[@theroadbacktonature]

Great Video, thank you. Curious, why do we need two separate tables - order_menu_item and food_order, can we not merge them together into single table?

[@DatabaseStar]

You're wleocme! The reason for two separate tables is because the food_order represents the whole order that was place: a reference to the restaurant, who placed the order, when it was placed, and so on. The order_menu_item is a list of all of the items included in the order, because there can be many. For example, a food_order from McDonald's could contain an order_menu_item of 1 cheeseburger, and another order_menu_item for 1 large fries.

[@rohith7929]

Can i assume order_menu_item table similar to cart

[@DatabaseStar]

Yeah that’s right, it’s a similar concept.

[@razhirsangasary]

what happens if the customer update address after place order?

[@DatabaseStar]

Good question. I think the app would have to handle that, and perhaps we need some kind of history associated with the order.

[@mr.adamprince4853]

what application to make the table relation? thanks

[@DatabaseStar]

I used a program called LucidChart

[@mr.adamprince4853]

@@DatabaseStar thank you

[@MohammadLsk]

Awesome , can you design database for a travel agency and reservation app

[@DatabaseStar]

Great idea! I'll create a video in the future.

[@fnoodes]

can I have a code for this example?

[@DatabaseStar]

I haven't created SQL code this example.

[@puBo-ik9uw]

Hello, I would like to ask if you want to add a column called totalprice (including the total price of the order and delivery fee), where should it be added? And how to use the syntax to achieve the purpose of calculating the price? follow the syntax is how I try, but I can't insert into the totalPrice Successfully SELECT SUM(order_item.ORD_ITEM_QTY*menu_item.MENU_ITEM_PRICE) as total FROM order_item INNER JOIN menu_item ON menu_item.MENU_ITEM_ID = order_item.MENU_ITEM_ID GROUP BY ORD_ID; This has bothered me for a long time and I can't figure it out Thank you in advance

[@DatabaseStar]

Hi, good question. I believe the column "totalamount" in the food_order table would do this. What error are you getting with that query?

[@0xmahabub07]

can you plz make a video on train ticket online reservation system Where trains can have different different routes and common too. Also how can I calculate the point to point price calculation Assuming the railway all routes as a undirected graph edges 😢

[@DatabaseStar]

Good idea! I can create a video on this kind of design.

[@0xmahabub07]

@@DatabaseStar 💚 waiting 🙂

[@nicholassmith6412]

Love your videos as an SQL newbie! Quick question: for requirement 5, why can’t the food_order table just have an order_status column rather than have a separate order_status table?

[@DatabaseStar]

Thanks! Good question. The reason is that there is a specific list of statuses that are allowed. For example, "Ordered", "In Progress", "Out for Delivery", "Delivered". These are stored in the separate order_status table so that there is consistent data for the orders. The alternative would be having an order_status column in the food_order table. This could work, however there is a chance of different values appearing. You could have a value of "Ordered", or "ordered", or "order placed" or "Done", or anything else that doesn't match the defined list. This makes the data hard to work with. You could put a check constraint on the column, but there are different issues with that (e.g. harder to make changes). Hope that answers your question.

[@nicholassmith6412]

@@DatabaseStar that's the exact answer I was looking for - makes sense, thank you!

[@hyderoxxx]

@@DatabaseStar thank you for the amazing video, I really appreciate it! Regarding the status question, what do you think about using an ENUM type in the food_order table for the status? It would restrict the possible values to a specified list. It would probably make it more error prone to compare status values between tables though. It is a topic that comes up often during my work and it would be helpful to see what you think.

[@IamDoQtorNo]

Great videos/content. Your deliveries are SPOT ON, to include your following on comments from your subs. I want to setup a mysql server box off site from client for replication. Do you have a video about those setup?

[@DatabaseStar]

Thanks, I'm glad you like it! I don't have a video on that setup, unfortunately.