#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, uh, 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, I feel like couple minutes of Googling, I found this, uh, online tool, db diagram dot I owe. And I think it looks good. And the cool thing is that I can, uh, uh, type the words here and, uh, it'll be displayed, uh, 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, uh, here's the design sketch, and, uh, I guess I'm going to start. So let's look at the first screen. Okay, I see that we have, uh, internationalization. So we'll have to have different languages log in, shopping cart. And, uh, here's the menu. So we have menu items and uh, uh, categories. Okay, so let's start building. They're going to have, uh, uh, menu categories, And they're going to have, uh, menu items. Now, a menu category is going to have, uh, an id, a 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, it'll have a name, a description, and the price. So name, uh, string, uh, description, text, and the price integer. Okay? And it'll be not parent id, it'll be menu category id. Okay, so we have a menu category and the menu item. Uh, 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 in, uh, email stream, whatever user. Okay. What do we have next? Okay, a few options that can be visible after sign in. So these are adjust the pages, like the sign in pages doesn't matter much. Okay? Just the devise, uh, page stylings doesn't matter much. Okay? After sign in, repeat your last order. So a user will be able to have, uh, many orders, and he has a shopping card. So we will need an order model. So we'll have a table order ID in, uh, user ID int and a reference user id. Okay, so an order belongs to a user, makes sense, and, uh, an order. We'll be able to have, uh, many other items. You see, we have other items. So an order can have, uh, different menu items, and it'll have like many menu items as other items. So we'll create the NU table, table order item ID in. It'll belong to an order and to a menu item. So order ID and menu item.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, a 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, um, and order will have a total amount of total price. That'll be an integer. And again, the prices of menu items can change, uh, in the future. So inside each order, we need to save, uh, the exact price the menu item had at the moment of ordering. So, uh, we'll also add the price into the order item. Okay, so we are going to go to our order item and, uh, add the, uh, menu item price. I think if we can just go with the price integer. Okay, so a user has an order, an order has many order items. Uh, an order item is basically a menu item, uh, 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 sauce, 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, uh, 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 through the first, uh, design flow. And here's the order flow. Let's see. Okay, here are some options, uh, that can be visible possible when they hover on a menu item. So here we see actually the ingredients. Here are the ingredients. So a menu item can have, uh, many ingredients. So we would have an ingredients list and, uh, a joint table between the menu item and ingredients. Okay? Now it's becoming more complex. So we'll add a table ingredients, ID integer, uh, name string, and our menu item will have, uh, many ingredients through ingredient items. All this can actually be, uh, recipe receipt. So I will create table, uh, in menu item ingredients ID integer, or actually, this one might not need NID because it'll be just joint table. And we would have, uh, ingredient ID integer and how we type it here, ref ingredient id. Okay? So, uh, an ingredient can belong to many menu item ingredients. And, uh, a menu item will also be able to have many menu item ingredients. So we'll have menu item ID and menu item id. Okay, so here we have, uh, a joint table starting to look quite complex. Now, um, let's just move this up here so that it looks a bit nicer. Okay? So many items can have many ingredients through many item ingredients. And you see we have these, uh, flavors. Now some can be, uh, added as multiple. So we can add multiple check boxes, and in some cases it'll be either or. And we can also add quantity. So we would add the quantity to this 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'll 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 card. Okay? So an item will be able to have, uh, different kinds of attributes below this. Let's keep going. Preview your order in your card. 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 add something like, uh, total price of this order item. Well, it can be done later on. Uh, let's see. So we have two flows. Click on order for not signed end user. Okay, so apparently these, uh, 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 you have delivery addresses. So, um, a user would have a delivery address or possibly many delivery addresses. Now, many delivery addresses sounds, uh, uh, like it makes more sense. So we'll create an additional table delivery address ID in. And what is it going to have? So it'll 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 the, that delivery can be different from the user. So one user can, uh, have one delivered to one username or to another username. So let's say I deliver address will have a first name string, uh, last name string. Then, uh, phone number string, I guess, uh, then street. Then um, house number. Actually this could just be address line, 1, 2, 3, 4, um, 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 you deliver address has been saved. Yeah, so delivery address belongs to a user. So it'll have user ID int and reference user id. Okay, something like this. Now, it doesn't look very beautiful, but that's, uh, okay, we can move it here, for example. Okay, what do we have next? So we have the same in once again. So this is 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, uh, an order can have a delivery address. Uh, a user can have many delivery addresses, and we can tie a specific, uh, delivery address to a specific, uh, order. So we would say, uh, inside our order table, we would have delivery address. Id, ID, okay, something like this. So a deliver address, uh, can actually have many orders. Yeah, it's normal because we can, well order the same, uh, stuff to the same address many times. Okay? Then again, quantity price, total price. And we have something like discount code. Okay? Then we have, uh, some data about the delivery person. So, uh, actually it'll 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, uh, be able to modify the time and the date. So actually it'll be just order time and if it'll be set to time now or if we want to override, we can, uh, change the time. So we would have, uh, something like order time inside our order and it'll be a date time. Okay? Then, uh, what else do we have here? We have a discount code. So, uh, possibly an order. I would be able to have a discount code id, so I would add the discount code id here the end and would belong into a discount code. So we would also possibly need a discount codes, uh, table. Okay? So we're creating a new table, table discount code id, then it would have, um, name, so it would be the one that would be inputted here. It would have something like active or not. So this would be ink string active. It would be a bullying. Uh, 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, uh, usages, integer, and uh, discount percent, it would be also integer gas. Now that we have our discount codes, here they are. Okay, now let's actually make it a bit wider. Okay? Something like this. Uh, go next. So we have your order discount code has been applied. Discount code was invalid. Okay? So we have the price without discount code, and we have the price with the, that discount code. So, uh, our order is going to have, uh, the total price and total price, uh, uh, after discount. So to be some like final price, let's say, say price and price with discount integer. And we can also go, no, let's not cope with the discount percent here. We'll just have the discount ID and hopefully it'll not be editable. So usually a discount code can be edited and it cannot be deleted if it has been used any times. Okay? So an order has the price, uh, uh, without discount and with discount, let's say without, okay, something like this. And what else do we have? Okay, click, uh, confirm an 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, uh, 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 am not sure how to do it with this synex or this application, but basically it'll have a status of, uh, maybe we can make some comments. Okay? So status will have, uh, options of something like, uh, uh, incomplete. Then it'll also be able to be, um, in delivery, okay? Uh, complete or like ready to process. Let's maybe add it to different lines. So incomplete, uh, ready to process or like ready to start. Then we will have, uh, uh, ready, uh, to deliver then, uh, uh, delivery and complete. Okay, so incomplete, meaning the user hasn't, uh, posted the order yet. Ready to start means the user has posted the order and they can start the, let's say, preparing it ready to deliver is they 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, uh, a map with the location of the restaurant, with the location of the client, and, uh, potentially we would maybe want to delivery guide to, um, shahi location, but it's not something urgent, okay? We have other updates. So, uh, 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 uh, we can also add his phone number to the user's model, not only to the delivery address, but we can live 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, dropdown menu, past orders, my deliver address, okay? So he will have the users deliver addresses here, he will see his, uh, past orders. So the orders that have the status, uh, either complete or incomplete, and the current orders, well, so the user will see all his orders, not much else here. Then, uh, you can enter his delivery address and see a preview of his location looks fine. Uh, success message when it was saved doesn't matter. Okay? Past orders and the order statuses. 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 o order items count inside the order. So here we will have, uh, order items count in the share here will have some kind of contact caching. I'll go the quantity of items than deliver the order date. So is the order time here? And yeah, we can also actually track, uh, the time when each of these statuses was changed. So when the order was, uh, uh, set to, uh, ready to start when 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, um, track time for each status change. Okay? And there are a few options. So reorder, okay, we've been here already. Uh, 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're done this first, uh, giant sketch, not have this other store menu. Let's have a look at this. Okay. So it's basically just, uh, 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, um, we have new canceled in preparation ready for pickup. So instead of ready to deliver, we will have ready for pickup. Then, uh, we have in preparation. So in, in preparation, then they'll also have something like canceled new. So new is something like ready to start on the way is, uh, going to be instead of delivery. So on that way, and here we have the estimated delivery time. We should also possibly calculate this somehow. I'll just leave it as a 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 way delivered looks fine. So this is, is the way the, I guess, restaurant, uh, manager or the cooks are going to see their orders. And again, here we have these flavors. So vegetable, milk or sauce or something else. So this is the dashboard for our start income and orders. Okay? So actually they'll need roles for staff and roles for, uh, clients. So I think it doesn't make sense to create two separate user models, but, uh, like for admins and for users, uh, it would be just good to give, uh, some users the role of, uh, delivery guy or role of stuff, or role of uh, uh, customer Or possibly a user could have many roles. Okay? Then depending on whether the price line and price store is filled out, the item will appear in install 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 store price can be different, but I think we can, uh, watch this one later. And you see an item can have an image. I actually forgot this. So a menu item has an image. They'll actually have an active storage relationship to an image. So menu item has an image, I think one image is enough, uh, and we are going to store it in, uh, something like active storage, and we'll have just a blo relationship that a menu item has one image. So Then name, price, store price in line. 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, uh, more interesting. So there are options. It has a field, a price, and, uh, some kind of thing that we can click. And the add-ons. So an item can have many options and many add-ons. The difference between an option and add-on is that, uh, an item can have ma 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, uh, definitely create an, 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 effect doesn't matter. Okay, this is interesting. So product availability, unique options, and a don'ts are listed here and can be displayed globally. Okay? So, uh, basically options and add, dons should, uh, have a, an availability status, Possibly as well as ingredients. And if the disable the availability of an option or a dawn, then all the products, uh, or uh, should not have the option to select this at dawn. So, for example, if you don't have meat, then, uh, they should not be able to select anything with meat, or if you don't have me vegetable milk, they should not be able to select anything with, uh, vegetable milk. So for now, I will just say, uh, ingredient availability, let it be ingredients. So I will say available bull, okay? Uh, individual options and adults 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, uh, an attached image. I don't see any difference between these two images. Okay? Then, uh, links to edit and delete a menu item and to make a menu item active or inactive. So actually we can add this active pool 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, uh, add don'ts. Okay, so let's go back to our landing page and possibly find the if view where we can select these, uh, add don'ts. Yeah, here is an example. So you see, uh, some we can select either hot or cold. So this would be an option. And, uh, this would be an add, don. We can select multiple, add don's and the options and add, Don's can also have their price. Okay, so let's, uh, make some notes about, uh, that. So, uh, option, uh, can be one, like hot or gold and, uh, add. Don can have many like vegetable milk, like, uh, no soy or whatever. Okay? So, uh, a menu item can have many options. And, uh, addons and then selecting an order item, we can select the ones, uh, that we want to add. So let's try to add this. I will say, uh, table, uh, uh, menu item options ID integer, and it'll 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 menu item options. And, uh, an option will be able to have a name string, and it'll have a price that can be actually new. Uh, so price integer and, uh, available bullying. So we can, uh, switch on menu item option availability or of, and a menu item can have, uh, how many can have, uh, one option. So it should be not one to 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, uh, inside our order item, whether we have it, it is going to have a menu item, option id, integer and referencing menu item, option id. Now, how do I make it more visible? Something like this. So a menu item can have many menu item options, uh, but an order item can have, uh, just one menu item option. It has a name, a price, uh, availability. Actually, if we go deeper, we can also link these ingredients to our menu item options. So, uh, if, uh, an ingredient is not available, then the, the menu item option will not be available, and all the menu items will not be available. And same with the add dolls. So a menu item can have, uh, many addons and an order item will also be able to have many addons. So it'll be something like, uh, whether we have our menu item options, we'll create a table menu item. Dons also name price available billing belongs to menu item. Now that you have this menu item at Don, like this, okay? And it'll have a relationship to our order item. So an order item can have many add dons. So order item, menu item, add don a table that will be named the, that will 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, uh, as a hash, if we have, uh, a menu item, a don't field, that is a hash inside our order items, and that will have the I ideas and s no, I think it'll be actually easier with the add an additional table. Okay, so they'll have a new table menu order item, adults ID integer, and it'll reference, uh, an order item id. We have the stable, where was it added? Okay, so order item at don, and it'll also reference this menu item at Don, menu, item at Don id and menu item at Don id. Okay, so I think we're done. Let's look at the flow. Basically, uh, a menu item belongs to a menu category. A menu item has a name, a description at default price, uh, availability, and it has many options, and it has, uh, many addons. Okay? Then, uh, then 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, uh, an option, and it can have many addons that are related to this menu item. Then the order itself to have a status. They'll have, uh, uh, the quantity of audit items, the price without discount, with discount, uh, yeah, and actually this price will have to be calculated based on the menu item price plus the price of the options. And a don't, this is important to, to note, okay? Then it has the order items, count price without discount, with discount, the discount code id. Then a user can have many orders, and actually they'll have, uh, a few additional, uh, timestamps. So not only the order time, we'll have a timestamp for each of the statuses. So here we have status string, and for each, uh, of the statuses, we would have, uh, a separate timestamp. So actually we would possibly want to add, uh, all these to our table as attributes. So, uh, uh, we would say like, um, canceled time, date, time. Then, uh, ready to start time, date, time, uh, in preparation time, date, time. I'm just adding time here so that when we develop the application, we always know that these are datetime fields, but not, uh, buildings or integer or whatever. So, um, ready to pick up time, date time, and, uh, so when it was picked up, so on the wait time And, uh, delivered time, And this will be the order time, so they're ready to start time. Okay? And this all actual doesn't, uh, doesn't include, uh, anything connected with money. So, uh, at some point the user would have to pay for the order, and the order would have to have a payment status. So, um, this would, I think, be managed by the, uh, payment provider, but we could add an additional status. So incomplete, uh, then, um, we would have some status for like payment, uh, uh, 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, uh, statuses now. Uh, just you would be changed, ready to start. And yeah, we have our, Our date fields for each of, uh, the available statuses except of incomplete. For this one, we don't need the, uh, timestamp. Okay, so looks good. And a user can have many deliveries addresses and, uh, for each order, the user specifies a delivery address. Okay. Looks, uh, quite clear to me. Now, one thing that we could also add again in the future is, uh, connect, uh, menu item addons and options to ingredients. So this would be something like a polymorphic table. So it would be like item ingredients and it would, uh, be able to reference item, menu item, or option or add, do. Okay? So I think we are done with modeling this, uh, application, actually, like the experience of using this tool. So have got all our tables, the data types, the relationships, and, uh, we can start, uh, creating an MVP with the draft in this hole inside the, our scaffolds in the rails application, for example. But, um, I guess that's it for now. So, um, thanks for being with me. And I think now, uh, you can also try to, well, you should have an imagination of how to use like entity relationships, diagrams, how to, uh, model, uh, data, uh, into a database. And now you can, uh, start creating scaffolds and, uh, modeling an application for a restaurant, ordering, uh, and delivery service. So thanks for being with me. Cheers.