How can I combine these tables?

Question

I have my system working with drinks, and now I'm ready to add burgers and other types of items.

Right now I have 1 orders table with separate item and itemorder tables for each type of item.

This is not sufficient because it is difficult to combine the items into 1 order.

Can you suggest any better way to do it? I'm confused about how to combine them because drinks and burgers have different options, and when I will add more and more items I expect a larger variety of options.

What's the most efficient way to do it?

---

EDIT 1:

I combined the item tables, combined the itemorder tables, and added a table with the price and surcharge information so that it won't be stored as often.

I feel it can still be improved.

---

EDIT 2:

I decided to remove all enums (thanks to Vérace and sibert), and I've improved my price structure (thanks to Lennart), but I'm still trying to determine whether the price table and its relationship are as good as they can be. The itemorder table now refers to priceid so I think it will allow me to correctly retrieve historical itemorders with accurate prices.

I still feel it can be improved.

Answer 1

Rule of thumb 1: If the table contains stuff that may change (edit, delete or add columns) in the future, it probably belongs to the data level.

Rule of thumb 2: Always have an unique key to every table. It is simpler to edit order records if there is a unique key for each row.

Rule of thumb 3: Rules of thumbs is not always the best way to do things. It always depends...

One way to struct this:

article_type

type_id   1          2         3
type_desc Burger     Drink     Extra stuff

EDIT 1 ---> You do not need to have drink categories. You can have Burger Big, Drink too much etc as several types. The article is the king with all information gathered. Select article and you have all information you need.

article

art_id    1              2               3             4
art_desc  Burger Big     Burger Small    Burger King   Drink little
art_type  1              1               1             2
art_price 50             40              60            10

EDIT 2 ---> I can see no value (at this point) to have a separate price table. The price should be attached to the article. Which also means one less column in the order table.

order

order_id     11
order_status 1 (int) Lookup table
order_date   2018-01-01

order_rec

ordrec_id    1
ordrec_order 11
ordrec_art   1
ordrec_sum   50

When fetching an order you can sum on-the-fly. No need to save sum at order level.

SELECT order_id,sum(ordrec_sum)
LEFT JOIN order_rec ON ordrec_order=order_id
GROUP BY 1
WHERE order_id=11

article_type can be useful if you add more groups of meals to the articles.

Answer 2

I think that your second edited schema is way too complicated. Have a look at this simplified version and if there are still problems from your perspective, let me know:

CREATE TABLE category
(
  category_id INTEGER PRIMARY KEY,
  category_name VARCHAR(25) NOT NULL -- ex. Burger, could be Small Burger &c.
);  

And then you have the other tables:

CREATE TABLE order_  -- note TRAILING underscore, order is an sql keyword, always worth avoiding even though certain systems have tricks whic allow you to use the - NEVER DO THIS!
(
  order_id INTEGER PRIMARY KEY,
  table_id INTEGER NOT NULL, -- no FK, could be "bar" or "special", i.e banquet
  main_server INTEGER NOT NULL, -- FK to staff table
  final_bill DECIMAL(5,2) NOT NULL, but can be 0 for special arrangement
  order_note text -- notes good or bad (ex. food sent back)
  order_final_bill -- computed/virtual field
);  

CREATE TABLE order_item
(
  item_id INTEGER NOT NULL, -- FK to item table
  item_category NOT NULL, -- FK to category table
  item_desc VARCHAR(25) NOT NULL,  -- FK to item table
  item_price DECIMAL(5,2) NOT NULL,  
  item_cuisson VARCHAR(50),  -- how item is cooked, i.e rare &c.
  item_note TEXT  -- extra info, allergies, change of sauce &c.
);

Not sure why you want an historical trail of item prices - restaurants are normally moving to fast for this to be of great interest - seasonal changes in pricing, fashion. Prices might even be determined on a weekly basis, e.g. before and just after Christmas and then into New Year (quiet time).

I would have an old_price table and when an item chages price, use a trigger to put in the item_id, the price_date_from and price_date_to as follows:

CREATE TABLE old_price
(
  item_id INTEGER NOT NULL, 
  item_price DECIMAL(5,2) NOT NULL,
  price_date_from DATE NOT NULL,
  price_date_to DATE NOT NULL
);

Something else - sometimes the same dish can have different prices at different times on the same day (lunchtime or dinner) or even at the same service depending if it's on a fixed menu or à la carte. I think, again, there's overcomplication here. A manager/owner who's fixing prices on a Monday morning won't really care about the price 3 weeks ago. All they'll care about is how much they're paying for goods this week and what the turnover will be. Just a few thoughts and don't foget YAGNI!

Best of luck with your project. You might also want to use a search engine to look at Open Source restaurant systems out there and see what you can learn!

p.s. SQL is untested (commas, semicolons) &c. Finally, if this is a new project, I would strongly urge you to look at PostgreSQL instead of MySQL - it's a vastly superior database!