0

What would be the best database system to store a lot of food dishes. Knowing that a dish can have variations. A small pizza is cheaper then a large pizza. And if you for example want extra olives on your pizza, you need to pay extra.

I am doubting between MySQL (which I am already familiar with) or MongoDB (of which I have heard good things but have zero experience in).

In MySQL I would use the following structure:

MySQL structure for dishes

You have a dish, and each dish has their variations. The price is stored in the variations. There is also a table which holds the extra options and the extra price for that options.

In MongoDB I guess I'd make a document like this:

{
    id: 1,
    name: "Pizza Hawaii",
    description: "Pizza with cheese and pineapple.",
    variations: {
        "small": "9.50",
        "medium": "12.50",
        "large": "15.00"
    },
    extras: {
        "olives": "0.50",
        "extra_cheese": "1.50"
    }
}

Which seams pretty flexible. But I have no clue on what technology would suit this project the best.

Edit:

Some SELECT examples. Let's say I want to replicate the following purchase-window.

Pizza purchase window.

I'd use the following SELECT to display the name and description of the Pizza:

SELECT name, description FROM dish WHERE ID = 1;

+--------------+---------------------------------+
| name         | description                     |
+--------------+---------------------------------+
| Pizza Hawaii | Pizza with cheese and pineapple |
+--------------+---------------------------------+

This select to get the dropdown menu for small/medium/large:

SELECT variation, price FROM dish_variations WHERE dish = 1;

+-----------+-------+
| variation | price |
+-----------+-------+
| Small     |   9.5 |
| Medium    |  12.5 |
| Large     |    15 |
+-----------+-------+

And the last query to display the checkboxes with all the extra options:

SELECT extra, price FROM dish_extras WHERE dish = 1;

+--------------+-------+
| extra        | price |
+--------------+-------+
| Olives       |   1.5 |
| Extra Cheese |   1.5 |
+--------------+-------+
6
  • Show us some SELECTs.
    – Rick James
    Commented Oct 14, 2022 at 22:56
  • @RickJames View edit.
    – O'Niel
    Commented Oct 14, 2022 at 23:12
  • Did the user come to the website with "I want whatever id=1 gives me"? Perhaps there is another SELECT from a page that listed Pizzas, then one that listed varieties of Pizzas?
    – Rick James
    Commented Oct 15, 2022 at 0:26
  • @RickJames The user comes to the website selecting "I want pizza/sushi/kebab/..." and then get a list of restaurants. In this case he selected restaurant with ID 1. It is NOT the case that a list of dishes is listed before a restaurant is selected.
    – O'Niel
    Commented Oct 15, 2022 at 1:09
  • If you are presenting a few lists (restaurants > menu items > options), there is no "best DB. Any of them will suffice. Easy option: use the DB you are familiar with. Learning option: use a different DB.
    – Rick James
    Commented Oct 15, 2022 at 1:30

1 Answer 1

2

FWIW, this is a common problem typically handled by RDBMS and I don't see anything specifically advantageous about using a NoSQL solution (like MongoDB) over a relational database.

The different Toppings that can be chosen for a pizza are a type of dimension for Pizzas. They inherently have a one-to-many relationship with an ordered Pizza. The different Sizes are another dimension of Pizzas. The relationship is one-to-one.

I'd actually model this similar to how a custom manufacturing schema would look like. A configurable Pizza item has multiple children Toppings items. Once ordered, you can save that configuration to a BOM (Bill of Materials) type of table that stores configured Pizzas. Size is a property of the parent Pizza item. Each item has its own UnitPrice which gets multiplied by the Size and / or factors such as double-toppings being selected, etc.

The schema for this type of problem is fairly standard and doesn't change frequently. NoSQL databases are useful for ill-defined schemas, schemas that change shape frequently, or schemas that are outside the control of the developers (such as consumption of 3rd party APIs) and that are liable to change often as well.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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