I am having trouble figuring out the best way to do this. My site has multiple restaurants. Each restaurant can have multiple menus, each menu has multiple categories, and each category has multiple items.
There are similar menus between restaurants (breakfast, lunch, dinner, etc), and similar categories between menus (appetizers, entrees, desserts, drinks, etc), and similar items between restaurants
and every item has some picture or videos.
Here is the schema I imagine. Is there a better way?
I would advice against all the ID tables at the end linking everything together. The point is probably to re-use categories between restaurants? But what happens when one restaurant renames the category? It will be renamed for all restaurants!
So I would add catagoryId to items, and menuId to categories and then give each menu a restaurantId so every menu is completely custom and everything on there can be changed without affecting any of the other menus in the system.
Thank you for your attention
Do you mean the following?
RestaurantMenu
RestId
MenuId
MenuCategory
MenuId
CatId
CategoryItem
CatId
ItemId
…
For example, in the following way without the Id of the restaurant, how can we specify the custom menu for each restaurant?
Menu
Id Name
1 dinner
2 lunch
.
.
etc
Category
Id Name
1 Seafood
2 fastfood
.
.
etc
Suppose the first restaurant serves both seafood and fast food in the dinner menu, but at the second restaurant in the dinner menu only fast food is served. In this case, the communication tables are not enough.
Yes. But at least won’t run in the issue that when one restaurant changes the name of a category, all restaurants that have that category also see that category renamed all of sudden. That just doesn’t make sense.
Also, storage space is dirt cheap, certainly when compared to developer time trying to come up with shared everything that works around the renaming problem.
Generally speaking storage space is the last thing you should optimize for.
Assume we have restaurant A and restaurant B. Assume restaurant A already has a menu, and it has a category starters, let’s say with ID 1.
Now restaurant B is creating a new menu and they’re adding the category starters. In your schema they would re-use the category from restaurant A with ID 1. Now, suppose that the person who enters this decides starters is not a good name and renames to appetisers. To him everything will seem fine, but for restaurant A the starters have also been renamed to appetisers, because they’re linked to the same category in the database. Of course restaurant A could change the name back to starters again, but then restaurant B will see a change they didn’t make. Etc. Ad infinitum.