What is the proper structure to use here?

Hello,

I have a client (Restaurant) who sells several meal courses. Each meal has its own additions (which the customer choose and pay extra for through the order).

Table Meals
id
name
unit_price

Table Additions
id
meal_id (foreign key)
name
unit_price

Table Customers
id
name
Phone
Address

Table Orders
id
customer_id
date
delivery_date_time

I have a problem in finding the proper table structure through which I can specify that the order has these meals with these additions for each added meal. I highly appreciate your help.

Thanks.

I know I need other tables …

As I need to show that :

Order n for customer x contains

1- Product z (with addition a, b ,c)

2- Product Y (with addition d , e ,f)

and so on …

How can this be reflected in a database structure ? Thanks.

You might find it easier if you use and ERD (Entity Relationship Diagram)

Click the image to see a larger version.

You’re right :slight_smile:

Well you would still want the foreign key because there may be other data that while not critical is important, is good to have a reference back to. I’m really talking about replicating data at the time of purchase that is critical to book keeping, not the entire state of the item. In regards to an order the usual suspects are the something to identify the product in a manufacturer catalog, purchase price, name and quantity. With that information if anything happens to the product row the historical data is still in tact for the order. Knowing the manufacturers number for the product is enough to if worst comes to worst know what was purchased. Without that data, who knows if something happens to the original product it relates, such as being deleted. Though soft deletes are normally the best way to go for this reason over hard deletes. That is another topic though, with its own ramifications such as conflicting identities of items being added and ones that are still present but have been soft-deleted.

It does, but then you don’t need the foreign key anymore IMO, since all you have is a copy of what a certain row looked like at a certain point in time, which may have nothing to do with the current contents of the referred row after a while :slight_smile:

Decide on what information you need to store in the tables and what the relationships will be between the various tables (1-to-many or many-to-many which I assume you understand the concept of).

Drawing an ERD (either by hand, which is simple for a small number of tables, or using a software application) as I suggested earlier should make it a lot easier to design your database structure visually.

Table Order Meals
id
order_id (foreign key)
meal_id (foreign key)
unit_price
quantity

Table Order Meals Additions
order_meal_id (foreign key)
addition_id
unit_price
quantity

You will probably want to store the unit price in the order for historical reasons. Otherwise, when the price is changed the data for the original item cost will lost. The same could be said about the name. You might want to add that to the order means and order meal additions table also. Just so you don’t have to worry about lossing or managing order records or pricing versions (what a mess). Generally it results in less issues later on to replicate data that is essential for record keeping purposes for orders. So if someone updates the price or deletes the product the business relevant data isn’t lost. make sense?

I guess you’ll need an ‘order details’ table