0

Consider a restaurant database where you have a table of restaurants (with some additional information) and for each restaurant (each row) in this table, you have a set of tables containing information (lets say menu etc..). Is it OK to have so many tables (with thousands of restaurants), or is there a better way to design such relationship?

for better imagination: example of such database

  • 1
    That is not a sustainable design. Every new menu would require an schema change, as well as an application change. – datagod Oct 4 '16 at 13:38
5

I'd go for something like:

restaurants table (stores all of your restaurants):
restaurant_id INT PRIMARY KEY
restaurant_name
etc

menus table (stores all of your menus):
menu_id INT PRIMARY KEY
menu_name

restaurant_menus table (stores which restaurants serve which menus):
restaurant_id INT, -- references restaurants.restaurant_id
menu_id       INT  -- references menus.menu_id

ingredients table (stores all available ingredients):
ingredient_id INT PRIMARY KEY
name
etc

menu_items table (stores each available menu item):
menu_item_id INT PRIMARY KEY
name
etc

menu_item_ingredients table (stores which ingredients make up a given menu item)
menu_item_id  INT, -- references menu_items.menu_item_id
ingredient_id INT, -- references ingredients.ingredient_id
name
quantity
etc 

menu_item_items (stores which items are on a given menu):
menu_id      INT, -- references menus.menu_id
menu_item_id INT -- references menu_items.menu_item_id

menu_item_items and menu_items probably need better names (dishes for menu_items, perhaps?).

  • Perhaps rename the menu_item_items to menu_lists or just catalogs? – ypercubeᵀᴹ Oct 4 '16 at 15:19

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.