Database Design for a Food Delivery App (Example & Walkthrough)
Vložit
- čas přidán 27. 07. 2024
- 📝 Get my Database Design Guides to many different sample databases: www.databasestar.com/dbdesign...
🎓 Learn how to design an effective database and create it using SQL: databasestar.mykajabi.com/edd...
Food delivery apps, such as Deliveroo and Uber Eats, have grown in popularity in recent years.
They are an interesting case study or example of an area to design a database.
In this video, we’ll learn how to design a database for a new food delivery app.
You’ll learn what some of the requirements may be for this fictional new app, see an overall database design (tables, columns, relationships), and how the design meets the requirements.
The aim is to help you understand how database designs can relate to the real world and help improve your own design skills.
⏱ TIMESTAMPS:
00:00 Introduction
00:37 Our design
01:07 Requirement 1 - order food
02:06 Requirement 2 - menu items
03:07 Requirement 3 - delivery time
03:46 Requirement 4 - address
04:34 Requirement 5 - order status
05:16 Requirement 6 - drivers
05:40 Requirement 7 - deliver order
06:07 Requirement 8 - rating
06:37 Other features
🔗 VIDEO LINKS:
ERD image used in this video: dbshostedfiles.s3.us-west-2.a... - Věda a technologie
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.
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.
Quora
Amazing videos!
Thanks!
you are really good teacher , hope u make more video like this
Thanks!
Great video!!. What about having reviews in a separate table. Because we might have index reviews based on just resturant or delivery driver
Yes, you could have reviews in a separate table, if you wanted to review just the restaurant or just the delivery driver.
Very simple and good design ! The design can be extended to add more things such as currency for instance.
Thanks! Yes that’s a good point, it can be.
Excellent Video!
Thanks!
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 ?
Hey, yes you could use that for the primary key instead.
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?
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.
Really helpful for my students
Glad to hear it!
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
Hi, good question. I believe the column "totalamount" in the food_order table would do this.
What error are you getting with that query?
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.
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.
Awesome , can you design database for a travel agency and reservation app
Great idea! I'll create a video in the future.
Can i assume order_menu_item table similar to cart
Yeah that’s right, it’s a similar concept.
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?
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.
@@DatabaseStar that's the exact answer I was looking for - makes sense, thank you!
@@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.
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?
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.
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 😢
Good idea! I can create a video on this kind of design.
@@DatabaseStar 💚 waiting 🙂
what happens if the customer update address after place order?
Good question. I think the app would have to handle that, and perhaps we need some kind of history associated with the order.
what application to make the table relation? thanks
I used a program called LucidChart
@@DatabaseStar thank you
can I have a code for this example?
I haven't created SQL code this example.
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?
Thanks, I'm glad you like it! I don't have a video on that setup, unfortunately.