I have been tasked with designing a cloud platform that allows a user to manage the menus of a chain of restaurants from one place. The menus are basically massive TVs at the ordering counter. These currently run MySQL and are updated manually.
What is the design pattern to store all of these store's menus in one database? For example, the name of the product, price and category(drink, burger etc.) is not going to be unique at each store.
The best solution I've come up with so far, is to add in a restaurant table and use the restaurantID as the foreign key in the products table and only sync the relevant products. However, this makes things difficult on the restaurant side because the product IDs will have big gaps in them.
Is there another way to do this? Or is this the general design idea for this sort of application?