How to design sql database with a subset of tables for each row in a main table

database-design

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

Best Answer

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?).

Related Question