Is my way of creating a entity relationship diagram correct?

Question

From what I understand, 1)table customer and table order should have a one to many relationship since customer has orders. 2)table order and table payment should have a one to many relationship since a single order can have multiple payments. 3)table order and table delivery should have a many to one relationship. Is this correct? Am I missing something else?

Answer 1

The design depends on your business requirements, but in general that sounds like a good design and valid for the context.

Some very minor things you can consider are the following:

1. In real life different Customers can technically live at the same Address, and therefore if you normalized CustomerAddresses to their own Table, it would reduce redundancy and theoretically can improve performance.

2. Some real life scenarios allow a Customer to use multiple Payment methods for the same Order. If this will never be a use case in your system then you can ignore this. But if it's possible you want to support that feature, then you'd need to update the relationship between Order and Payment so that it was one-to-many.

3. You may want to consider to split the Order table into two tables OrderHeader and OrderLine. Then there would be a one-to-many relationship between the two tables, since multiple lines can go on the same header. The header would store the general Order information like OrderId, OrderDate, OrderTime, CustomerId but the OrderLine table would have a separate line for each type of item ordered, and it would store the PizzaId and Quantity (and any other specific information, such as customizations to the item ordered).

   As it stands right now with your current design, your Customer can only order one type of Pizza per Order. By splitting Order into two tables like above, they'd be able to order multiple different kinds of Pizza on the same Order (or even other types of items that may be sold later on, like cheesy bread and soda).