How to store a restaurants menu in a database. Say suppose I have a database called hotel with a table called restaurant. Here is the schema.
restaurant(Type, Name, Description, Pictures, Address, Menu)
Type, Name, Description, Address are strings, like varchar. Pictures are blob type.
Now how do I store a Menu? Say that a restaurant has over a 100 items. It becomes meaningless to have a coloumn for each menu item. So how do I store it?
Can I use Json to overcome this? If so how?
Example of a menu:
Pasta: 5 euros
Pizza: 10 euros
.
.
.
similarly many items
And this is not a way I am looking for:
item(restrnt id, item, price)
The above will work well for a single restaurant with a large number of dishes.
You have a table of menu items, and one column of that table will be a foreign key which will match the primary key in the restaurant table to associate the menu item with a particular restaurant.
If menu items can be shared between multiple restaurants, use a junction table instead (a third table with two columns - both foreign keys, one on the restaurant table and one on the menu_items table.
Make a table items(id, name, description). This table will contain all the items you have in your restaurant. You can update it any time.
Make another table item_prices(id, item_id,restaurant_id, price, update_date). Here item_id is the id of item you are referring from items table. On the long run, this schema would prove helpful, as its easy to maintain. You just need to update the item_prices table for any price change.
Similarly you make restaurants(id, reatauran_name, ... )
And better store images in filesystem rather as blob.
I would start off with something like this, ofcourse it has to be edited to suit your needs.
item (id, title, description)
menu (id, restaurant_id, title, description, active_from, active_to)
menu_item (id, menu_id, restaurant_id, price)
picture (id, title, description, filename, active)
restaurant (id, type, name, description, address)
restaurant_picture (id, restaurant_id, picture_id)
Using a proper normalized model then one restaurant can have several menus, which can have several items.
I suggest you read up on database normalization.