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.
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.
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?
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.
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.
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.
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 ?
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 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.
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 😢
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
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?