#57 Database Architecture 101: Building an ERD (Entity Relationship Diagram) for UberEats clone

hello so today's episode will have nothing to do with rebound rails basically a friend of mine sent me a figma design sketch of an application that he wants to build it is a restaurant menu ordering and delivery application and i'm going to build an entity relationships diagram for this application based on the sketches so i'm going to build out the tables the attributes of the tables and the relationships between them and to do this i found the after like couple minutes of googling i found this uh online tool db diagram dot io and i think it looks good and the cool thing is that i can type the words here and it will be displayed as an image so i don't have to draw anything actually myself i can just drag if i don't like something okay so here's the design sketch and uh i guess i'm going to start so let's look at the first screen [Music] okay i see that we have internationalization so we'll have to have different languages log in shopping cart and here's the menu so we have menu items and the uh categories okay so let's start building they're going to have uh menu categories and we are going to have menu items now a menu category is going to have an id and name and a menu item is going to belong to a menu category okay so menu category has many menu items let's see yeah looks like this okay and a menu item will have a name a description and a price so name string description text and the price integer okay and it will be not parent id it will be menu category id okay so we have a menu category and the menu item next let's see what else we have so nothing else on that page okay we have user sign in so we're going to have users like device users i guess so we'll have a new table id int email stream whatever user okay what do we have next okay a few options that can be visible after sign in so these are just the pages like the sign-in pages doesn't matter much okay just the device page stylings doesn't matter much okay after sign in repeat your last order so a user will be able to have many orders and he has a shopping cart so we will need an order model so we have a table order id int [Music] user id int and a reference user dot id okay so an order belongs to a user makes sense and an order will be able to have many other items you see we have other items so an order can have uh different menu items and it will have like many menu items as other items so we'll create the nu table order item id int it will belong to an order and to a menu item so order id and menu item dot id okay now it's becoming a bit twisted so we are going to move this around a bit so a user has an order and menu category has a menu item and an order has many other items something like this okay what is next so there are the prices and there is the total amount of the order so i know that will have a total amount or total price that will be an integer and the again the prices of many items can change in the future so inside each order we need to save the exact price the menu item had at the moment of ordering so we'll also add the price into the order item okay so we're going to go to our order item and add the menu item price i think we can just go with price integer okay so a user has an order an order has many order items an order item is basically a menu item saved for the order and a menu item belongs to a menu category okay looks quite simple for now then we have the total price and here is we have these different options so plus vegetable milk plus source and so on so our order items or our menu items can have some additional flavors and we might want to add them later on here again this is something connected with flavors so one option costs 85 another costs 89. i think we'll have to dig into this later on or it might be visible somewhere here okay i think we've gone for the first uh design flow and here's the order flow let's see okay here are some options uh that can be visible possibly when they hover on a menu item so here we see actually the ingredients here are the ingredients so a menu item can have many ingredients so we would have an ingredients list and a joint table between the menu item and ingredients okay now it is becoming more complex so we'll add a table ingredients id integer name stream and our menu item will have many ingredients through ingredient items or this can actually be a recipe so i will create table in menu item ingredients id integer actually this one might not need nad because it would be just a joint table and we would have ingredient id integer and how do we type it here ref ingredient dot id okay so an ingredient can belong to many menu item ingredients and a menu item will also be able to have many menu item ingredients so we'll have menu item id and menu item dot id okay so here we have a joined table starting to look quite complex now let's just move this up here so that it looks a bit nicer okay so many items can have many ingredients through menu item ingredients and you see we have these flavors now some can be added as multiple so we can add multiple checkboxes and in some cases it would be either or and we can also add quantity so we would add the quantity to these order items so whether we have order items we would have price and quantity okay so we can add many menu items as an order item and again we have these uh additional attributes i think we will see more about them later but we have to remember this okay so what do we have here uh select an item choose additional attributes and add to cart okay so an item will be able to have different kinds of attributes we know this let's keep going preview your order in your code okay again what do we have here we have items quantities and price and total price and this total price will be calculated based on the uh price of each other item multiplied by its quantity actually here for it to be easier we can also uh add something like total price of this other item well it can be done later on uh let's see so we have two flows click on order for not signed in user okay so apparently these orders can exist without a user being signed in so a user id would not be obligatory and we could store this either in the session or actually create an order and later on when a user creates an account assign it to the user okay and here we have delivery addresses so a user would have a delivery address or possibly many delivery addresses now many delivery addresses sounds uh like it makes more sense so we'll create an additional table delivery address id int and what is it going to have so it will have a first name a last name a phone number uh yeah so a user can actually have many delivery addresses and the data about that delivery can be different from the user so one user can have one delivery to one username or to another username so let's say a delivery address will have a first name string last name string then phone number string i guess then street then house number actually this would just be address line one two three four entrance number apartment number and apartment okay and create an account for easy ordering so this is already just some controller action and what else do we have here so your delivery address has been saved yeah so delivery address belongs to a user so it will have user id int and reference user.id okay something like this now it doesn't look very beautiful but that's okay we can move it here for example okay what do we have next so we have the sign in once again so these are just the action flows and we are modeling that database for now we are not modeling the views and the controllers yet okay here is your order so an order can have a delivery address a user can have many delivery addresses and we can tie a specific delivery address to a specific order so we would say inside our order table we would have delivery address id dot id okay something like this so a delivery address can actually have many orders yeah it's normal because we can well order the same stuff to the same address many times okay then again quantity price total price and we have something like discount codes okay and then we have some data about the delivery person so um oh actually it will be delivered to yeah so it's not about the delivery person and we can say order now or order for later this is also interesting so order for now or order for later so how do we want to model this uh if it is order for later then we would be able to modify the time and the date so actually it would be just all the time and it will be set to time now or if we want to override we can change the time so we would have something like order time inside our order and it would be a date time okay then uh what else do we have here we have a discount code so uh possibly an order would be able to have a discount code id so i would add the discount code id here the int and it would be belong into a discount code so we would also possibly need a discount codes table okay so we are creating a new table table discount code id then it would have a name so it would be the one that would be inputted here it would have something like active or not so this would be string active it would be a boolean then we would have something like how many times it was used how many times it can be used so uh times used integer max usage as integer and the discount percent it would be also integer i guess now that we have our discount codes here they are okay now let's actually make it a bit wider okay something like this going next so we have your order discount code has been applied this code was invalid okay so we have the price without discount code and we have the price with the the discount code so our order is going to have the total price and total price [Music] after discount so to be something like final price let's say price and price with discount integer and we can also go no let's not copy the discount percent here we'll just have the discount id and hopefully it will not be editable so usually a discount code cannot be edited and it cannot be deleted if it has been used any times okay so an order has the price uh [Music] without discount and with discount let's say without okay something like this and what else do we have okay click confirm and order okay let's have a look at our order so here we have the order the total price price for discount and an order can have status of arrived so actually an order will have different statuses and one of them will be arrived so an order will have a status now can we define the options here i'm not sure how to do it with this syntax of this application but basically it will have a status of maybe we can make some comments okay so status will have options of something like uh incomplete then it will also be able to be [Music] in delivery okay [Music] complete or like ready to process let's maybe edit two different lines so incomplete ready to process or like ready to stop then we will have ready to deliver then delivery and complete okay so in complete meaning the user hasn't uh posted the order yet ready to start means the user has posted the order and we can start let's say preparing it ready to deliver is that we have prepared it and the delivery guy can uh well it can be given to the delivery guy or to the beta then delivery so delivery in process it has been delivered and complete it means it has been given to our customer okay looks fine now there are so many ways we can go here so thank you view all the status so we should also have a map with the location of the restaurant with the location of the client and the potentially we would maybe want the delivery guide to share his location but it's not something urgent okay we have all the updates so this would be already the data of the user so the user's email and possibly his phone number now at the moment the user has his id and email and we can also add his phone number to the user's model not only to the delivery address but we can leave this for later i think it's not something urgent okay it's an example of page not found what do we have in this index page okay an example of an error okay so we've done that flow and here we have another flow with settings so um drop down menu past orders my delivery address okay so here we'll have the users deliver addresses here he will see his past orders so the orders that have the status either complete or incomplete and the current orders well so the user will see all his orders not much else here then you can enter his delivery address and see a preview of his location looks fine a success message when it was saved doesn't matter okay passed orders and the order status is okay so one is getting prepared we can actually uh rename this to oh actually another status so that it is being prepared so getting prepared okay then we'll have delivered so delivered not complete okay and we can see the order items inside each order and we have the order date the quantity of items so we can actually add the order items count inside the order so here we will have order items count integer you will have some kind of conduction i'll go the quantity of items then deliv the order date so is the order time here and yeah we can also actually track the time when each of these statuses was changed so when the order was uh set to uh ready to start then it was set to getting prepared ready to deliver delivery and delivered so this would actually be really interesting now we can leave this also for a bit later so track time for each status change okay and there are a few options so reorder okay we've been here already what happens on the click on reorder we've been here already okay we've seen all these views uh already so looks fine i think we are done with this first giant sketch now we have this other store menu let's have a look at this okay so it's basically just the way the menu looks nothing special nothing that we haven't seen yet and again the languages okay and here we have the start and admin dashboard so also quite a few screens here let's have a look at them so we have new cancelled in preparation ready for pickup so instead of ready to deliver we will have ready for pickup then we have in preparation so in in preparation then we'll also have something like cancelled new so u is something like ready to start on the bay is going to be instead of delivery so on the way and here we have the estimated delivery time we should also possibly calculate this somehow i'll just leave it as a question mark for now so maybe we would want to use some kind of google maps uh api or something like that for the estimated delivery time okay so on the pain delivered looks fine so this is the way the i guess restaurant manager or the cooks are going to see their orders and again here we have these flavors so blood vegetable milk or sauce or something else so this is the dashboard for our starter symptom and orders okay so actually we'll need rolls for stuff and roles for clients so i think it doesn't make sense to create two separate user models but uh like for admins and for users it would be just good to give some users the role of delivery guy or role of staff or role of a customer or possibly a user could have many roles okay then depending on whether the price online and price store is filled out the item will appear in store menu or in online menu about okay this is kind of interesting so the menu online and the menu offline uh can have different uh prices so an online price can be one and a stop price can be different well i think if we can [Music] watch this one later and you see an item can have an image i actually forgot this so a menu item has an image will actually have an active storage relationship to an image so menu item has an image i think one image is enough and we are going to store it in something like active storage and we'll have just a blob relationship that a menu item has one image so then name price store price online okay for now we'll have just one price for the simplicity then category description and here are the options okay so here it becomes more interesting so there are options it has a field a price and some kind of thing that we can click and add ons so an item can have many options and many add-ons the difference between an option and a dawn is that an item can have many of one of them and one of the other i'm not sure which one okay we have a preview doesn't matter yet so here are some examples uh of options and add-ons i still don't understand them well yet but we can we can definitely create an additional table where we're going to keep the relationships of menu items and their options now we'll leave this for later because i don't fully understand it yet and what do we have here so drag and drop effects doesn't matter okay this is interesting so product availability all unique options and add-ons are listed here and can be displayed globally okay so [Music] basically options and add-ons should have an availability status possibly as well as ingredients and if we disable the availability of an option or add-on then all the products or should not have the option to select this at dawn so for example if we don't have meat then we should not be able to select anything with meat or if you don't have vegetable note we should not be able to select anything with the vegetable milk so for now i will just say ingredient availability let it be ingredients so i will say available boolean okay individual options and addons can be enabled or disabled for a product so we can also enable them and disable them on a product level okay we will also leave this in mind for later now what do we have here so yeah just drag and drop design again add new item item preview yeah just the way we can delete an attached image i don't see any difference between these two images okay then links to edit and delete a menu item and to make a menu item active or inactive so actually we can add this active boolean to our menu items like this so drag and drop effects for formatting menus doesn't matter yet and i guess that's it so the only thing that is left is uh these uh options and the add-ons okay so let's go back to our landing page and possibly find the view where we can select these add-ons yeah here is an example so you see uh some we can select either hot or cold so this would be an option and this would be an add-on we can select multiple add-ons and the options and add-ons can also have their price okay so let's make some notes about that so option [Music] can be one like hot or cold and add don can have many like vegetable milk like no soy or whatever okay so a menu item can have many options and the addons and when selecting an order item we can select the ones that we want to add so let's try to add this i will say table uh menu item options id integer and it will belong to a menu item so menu item id now that we have the stable where was it added okay here it is menu item options okay so many item options and an option will be able to have a name stream and it will have a price that can be actually nil so price integer and the available boolean so we can switch on menu item option availability or off and a menu item can have how many can have one option so it should be not one too many but one to one i'm not sure how to say it that it is one to one in this case well actually a menu item can have many menu item options but an order item can have just one menu item option that's the way it should be so inside our order item where do we have it it is going to have a menu item option id integer and referencing menu item option dot id now how do we make it more visible something like this so a menu item can have many menu item options but an order item can have just one menu item option it has a name a price availability actually if we go deeper we can also link these ingredients to our menu item options so if an ingredient is not available then the menu item option will not be available and all the menu items will not be available and same with the add-ons so a menu item can have many add-ons and an audio item will also be able to have many add-ons so it would be something like that we have our menu item options we will create a table menu item addons also name price available boolean belongs to menu item now that we have this menu item at don like this okay and it will have a relationship to our other items so an order item can have many add-ons so order item menu item add-on a table that will be named well that would have a very long name we can of course think of a bad name later on but uh for now how should we name it actually we can just uh try passing them as a hash so what if we pass them as a hash if we have uh a menu item at don'ts field that is a hash inside our other items and they will have the ids and params no i think it will be actually easier with the adding an additional table okay so we'll have a new table menu order item addons id integer and it will reference an order item id that we have this table where was it added okay so order item at done and it will also reference this menu item at dom menu item add-on id and menu item at don dot id okay so i think we're done let's look at the flow basically menu item belongs to a menu category a menu item has a name a description a default price availability and it has many options and it has many add-ons okay then when we want to order some menu items we add the order items to our order and an order item can have a menu item it can have an option and it can have many add-ons that are related to this menu item then the order itself will have a status it will have uh the quantity of ordered items the price without discount field discount uh yeah and actually this price will have to be calculated based on the menu item price plus the price of their options and add-ons this is important to note okay then it has the order items count price without discount with discount that discount code id then a user can have many orders and actually will have a few additional timestamps so not only the order time will have a timestamp for each of the statuses so here we have status string and for each of the statuses we would have a separate timestamp so actually we would possibly want to add the all these to our table as attributes so uh we would say like cancelled time date time then ready to start time date time in preparation time date time i'm just adding time here so that when we develop the application we always know that these are date time fields but not uh booleans or integers or whatever so um ready to pick up time date time and uh so when it was picked up so on the way time and delivered time and this will be the order time so the ready to start time okay and this all actual doesn't uh doesn't include anything connected with money so at some point the user would have to pay for the order and the order would have to have a payment status so this would i think be managed by the payment provider but we could add an additional status so incomplete uh then we would have some status for like payment uh uh [Music] ready to pay actually incomplete would be ready to pay so payment uh and ready to start would mean that the payment has been done possibly yeah so i think i won't be adding any additional statuses now just you would be changed ready to start and here we have our our date fields for each of the available statuses except over incomplete for this one we don't need the timestamp okay so looks cool and a user can have many deliveries addresses and for each order the user specifies a delivery address okay looks quite clear to me now one thing that we could also add again in the future is connect menu item add-ons and options to ingredients so this would be something like a polymorphic table so it would be like item ingredients and it would be able to reference either menu item or option or at don okay so i think we are done with modeling this application i actually liked the experience of using this tool so we have got all our tables the data types their relationships and we can start creating an mvp with drafting this fall inside the our scaffolds in the rails application for example but i guess that's it for now so um thanks for being with me and i think now you can also try to well you should have an imagination of how to use like entity relationships diagrams how to model uh data uh into a database and now you can start creating scaffolds and modeling an application for a restaurant ordering and delivery service so thanks for being with me cheers

Channel: SupeRails

Views: 307

Keywords: ruby, rails, ruby on rails, tutorial, programming

Id: NZlN73NEgWM

Length: 44min 57sec (2697 seconds)

Published: Sun Oct 10 2021