0

Orders database

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.

flat schema

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.

enums removed, some cleanup

I still feel it can be improved.

  • Using only ONE item_order containing both drink and burgers? – sibert Apr 29 '18 at 5:14
  • Does it mean the itemorder table should have several nullable itemid columns (1 for each type of item) which are nullable? And then some enum to indicate what type of item it is? I feel there must be a better way than this – user150205 Apr 29 '18 at 5:45
  • Why are you repeating attributes like price in the itemorder_ tables? – Lennart Apr 29 '18 at 6:27
  • Have a look at this. Just have a category table (beverage, main, dessert, snack, hot beverage, special... &c...), with each order item having an FK referring to category. Just have one item table - even if you have millions of items, this is not an issue. You're over complicating things! – Vérace Apr 29 '18 at 6:35
  • I just added a screenshot of my updated version, but @Vérace's idea seems great so I will try to improve using this technique – user150205 Apr 29 '18 at 6:40
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.

  • Thanks, that was very helpful and now I've added an updated schema. Let me know if I missed anything. – user150205 Apr 29 '18 at 8:49
  • Only one thing. You have missed the point to simplify :-) Use only ONE type lookup table with mixed drink and burger categories. And what is the purpose of the separate price table? – sibert Apr 29 '18 at 9:01
  • And by using several articles (item), you do not need to have size in the order records. The size information is in the article. – sibert Apr 29 '18 at 9:07
  • Yes, I'm still considering changing to your idea of single items with the surcharge inherent in the price, but that is such a major change so I'm thinking about what other consequences it would have. With separate surcharges it is easy to list things on the menu as "Item 1 = $2.00, +$0.60 for medium, +$1.20 for large". If items are truly separate it seems I would have to re-group them (maybe by matching items with the same name) to present the data in this way. – user150205 Apr 29 '18 at 9:18
  • Surcharges as you describes it, are three articles. Look at the edit. – sibert Apr 29 '18 at 9:23
1

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!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy