2

I am working on developing a MySQL database schema for a restaurant menu system inspired by platforms like DoorDash, UberEats, and Yelp. My aim is to design a schema that is flexible and can handle various menu items, options, and customizations, including time-based availability and portion sizes.

Current Schema

Menu Items Table:

id (PK) account_id name description price
1 1 Chicken Soup For the Soul 12.00

Menu Item Variants Table (for portion sizes):

id (PK) item_id name description price_adjustment
1 1 Large 12oz 3.00
2 1 X-Large 16oz 5.00

Menu Item Options Table:

id (PK) item_id name description price_adjustment
1 1 No noodles NULL -1.00
2 1 Sub gluten free noodles NULL 3.00

Menu Item Availability Table (for time-based availability):

id (PK) item_id weekday start_time end_time
1 1 NULL 00:00:11 00:00:15

Menu Items Add-Ons Table:

id (PK) item_id name description price
1 1 Wonton Spicy Wonton 3.00
2 1 Meatballs A Spicy Meatball 4.00

Challenge

I am struggling to efficiently manage complex menu items that require multiple selection options. For instance, consider the Chicken Kabob Plate, where customers can choose two sides from options like white rice, brown rice, hummus, salad, or pita bread. Similarly, the Poke Bowl presents challenges as it allows customers to select from a diverse array of ingredients to customize their meal. The current schema does not adequately support the configuration and administration of such customizable meals. This complexity extends to integrating these options into the system in a way that supports dynamic customer choices, simplifies menu updates, and optimizes query performance for these composite items.

Question

Can anyone suggest how to enhance or extend my database schema to better handle complex menu items like Poke Bowls and Chicken Kabob Plates with multiple customization options? How can I optimize the database design to ensure flexibility and efficiency in updating and querying such customizable items? Additionally, if there are other aspects or alternative approaches I might be overlooking, I’d appreciate your input on those as well.

Improve this question
3
  • 1
    its tricky. I'd just give you two bits of advice. 1. make time availablity per menu not per item 2. never expect the users to be able to manage their own menu without assistance. Your system will soon exceed the complexity ppl can handle without training. This does allow you too keep it simpler than it would otherwise have to be though, because you can add in complex stuff like inputting code for restrictions or adding extra features when a menu cant be represented
    – Ewan
    Commented May 1 at 11:21
  • The question is too open-ended to be meaningfully answerable, in my opinion. You do point out valid scenarios, but the closing question is more of an open "help me make it better". It would be better if you picked one goal or use case, try to implement a solution to it, and then come back with a concrete question as to why your attempt didn't achieve what you set out to do.
    – Flater
    Commented May 2 at 1:57
  • I don't know how you could come to that conclusion after reading Darren's answer. He answered my question perfectly and gave me more than enough insight.
    – tony
    Commented May 2 at 6:05

1 Answer 1

Reset to default
4

I look at it this way; your root item is your "Menu Item", and everything else is basically a "variation" of the menu item; from sizes, to add-ons, to options.

I think there's scope to simplify this schema by doing the following:

  • Remove "Menu Item Variants (for portion sizes)" table
  • Remove "Menu Item Options" table
  • Remove "Menu Items Add-Ons" table
  • Consolidate all of the above into variation groups, with variation group items

For example:

Item Variation Groups Table:

These would represent logical groupings of variations; sizes, noodle optons, addons etc. - basically anything you can think of.

If you think about it from the UI point of view, it represents each dropdown, or list of items, you may use to customise your menu item.

Using something like min_selection and max_selection, you can make a selection in the group optional (0 min), mandatory (1 min), or upto X selectable options.

id (PK) account_id name description min_selection max_selection
1 1 Sooup noodle options Options for your noodles 0 1
2 1 Soup Addons Popular addons for our soups 0 2
3 1 Soup sizes Sizes for our soups 1 1

Variation Group Items Table:

Here, you define the specific items that belong to your groups.

id (PK) variation_group name description price_adjustment
1 1 No noodles -1.00
2 1 Sub gluten free noodles 3.00
3 2 Wonton Spicy wonton 3.00
4 2 Meatballs Spicy meatball 4.00
4 3 Standard 10 oz 0.00
5 3 Large 12 oz 3.00
6 3 X-Large 15 oz 5.00

Menu Item Variation Groups Table:

And here, you assign groups to each menu item. The benefit of this approach is, if you have 10 soups on your menu, you can re-assign the same groups - no need to duplicate data.

If you need a variation for a particular item, you can introduce a new group, with some new items.

id (PK) menu_item_id variation_group_id
1 1 1
2 1 2
2 1 3

Benefits

  • You reduce the complexity of your schema, but increase flexibility
  • Groups can be re-used across similar menu items

This is, of course, just a suggestion - it may even jog your brain and help you think of an alternative.

Good luck!

Improve this answer
2
  • 1
    Thank you, that's exactly what I was looking for!
    – tony
    Commented May 2 at 3:07
  • 1
    Glad it was helpful @tony! Good luck with your project :)
    – Darren Taylor
    Commented May 2 at 9:41

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