4

I have this problem, which seems like it should have an easy solution, but I can't figure it out.

Say we have pizza_toppings table where a chef enters available sets of toppings. There are four toppings: pepperoni, olives, pepper, and basil. The chef makes four different pizzas with the above ingredients:

pizza_toppings
--------------
pizza 1 has only pepperoni
pizza 2 has pepperoni and olives
pizza 3 has olives or basil
pizza 4 has ( pepperoni and pepper) or only basil

How would I record all these 'and', 'or', 'and + or', 'and + and', '(and + or) + and ...', etc. relationships between table entries?

It seems like a problem from some difficult textbook that should have a clever solution, which I can't figure out. My program doesn't actually deal with pizza and there will be countless "toppings" created by users and infinite possible combinations, which will also be combined by users. So I can't really make a table with pre-defined combinations. Is there a solution to such a problem?

  • 1
    You're trying to build a schema to store an abstract syntax tree right? The trivial solution is you store it as text (like you have in your example). This would be OK if you're just doing some kind of key/value look up. E.g.: Find the "rule" named "foo" and tell me the AST that I can now use in my app. – Kent Chenery Dec 16 '17 at 20:17
  • @KentChenery Thank you for the hint. It does appear that I am about to fit an abstract syntax tree into a database. Unfortunately, I am expecting it to grow quite big and be read a lot so I think it will require indexes – Arthur Tarasov Dec 17 '17 at 13:18
  • I'm wondering the business value of placing an order with "olives or basil". Who decides what to put? Can I order a pizza with any three ingredients if one of them is basil and any two ingredients if you plan using olives? Or things like "you can use any ingredient, as long as you don't mix basil with pepperoni". We don't consider price, but you may say: "give me the most expensive pizza, but I hate pepper". To me, a pizza is an unordered list of ingredients. Bringing more complexity would not pay off. But if you still want to pursue your idea, I would store a logic expression as a string. – user140142 Dec 17 '17 at 13:58
  • Has only pepperoni? Or has pepperoni? Only is going to be difficult, that's exclusion. – Evan Carroll Dec 17 '17 at 17:44
2

Hmmm... interesting question.

As you say, you can have hundreds even thousands of different combinations of toppings, but you don't want a menu the length of 'War and Peace'.

I would divide the toppings into categories (cat_1... cat_5) and have your pizza price based on the top 3 categories on your pizza.

  • 1€ for cat_1 (salami, anchovies),
  • 75¢ for cat_2 (turkey, chicken),
  • 50¢ for cat_3 (pineapple, porcini),
  • 25¢ for cat_4 (capers, cherry tomato)
  • 0¢ for cat_5 (x 2 - standard tomato, mushroom)

So, you have a base price of, say, 5€ per pizza, then add two cat_1 and a cat_3 topping for a total price of €7.75.

Allow two "standard" toppings - say tomato and cheese as cat_5 and you have a relatively simple pricing scheme that even the stupidest staff member or client can understand.

You could also provide "specials" from time to time - i.e. "Normally, €7.50 - try it tonight for €6.50..." or suchlike!

You appear to be suggesting that pizzas are not your prime focus in life? Maybe if you explained your requirements in further detail, we might be able to be of more assistance. Having said that, I think the scheme above or similar would go a long way to helping resolve this or similar issues.

On a database level, I would have something like the following (fiddle):

CREATE TABLE order_
(
  order_id serial NOT NULL,
  order_time timestamp NOT NULL,
  order_taken_by INTEGER NOT NULL, -- references employee table
  order_served_by INTEGER NOT NULL, -- same
  order_value INTEGER NOT NULL -- in cent
);

CREATE TABLE order_item
(
  order_item_id SERIAL NOT NULL,  -- may be pizza_id
  order_item_price INTEGER NOT NULL
);

CREATE TABLE pizza
(
  pizza_id serial NOT NULL,
  cat_1_items VARCHAR (50), -- references table of cat_1 toppings
  cat_2_items VARCHAR (50) -- cat_2 toppings
  -- ..
  -- ..
  -- ..
);

You might have a stored procedure to calculate your pizza price? You might also find this interesting.

  • Thank you, your insight really helped. I realized this problem will not be a cheap one to solve. Categories seem to be a way to go. Unfortunately, 'the menu' is expected to be way longer than War and Peace so I might end up with several levels of categories. And then I need to make an encoder + decoder + GUI to connect it all to the front end. At least I know there is no trivial solution, which is very important to know when planning something like this. – Arthur Tarasov Dec 17 '17 at 13:11

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.