Hi, I am trying to design a restaurant point of sales system and I ran into some difficulties designing my database, hopefully I can get some guidance.
The Menu, I wonder how should I structure the database:
For example I have multiple categories such as Drinks, Burgers, Pizza, Dessert etc; and each categories I may have Sub Categories such as for Drinks, I can have Coffee, Soft Drinks, Smoothie. My question is how should I structure this? Data Type for Drinks and also a Data Type for each Sub Category and link it back to Drinks?
Another question is for example Burgers, i have list of Signature Burgers, what if I have the option of “make your own burger”, and I have a list of ingredients to choose from, how should I structure the database? Further more if I am able to allow customer to pick how the burger patty is cooked (medium rare, well done etc), how should I do this?
I recommend setting up Option Sets for Main Category options and Sub-Category options. You could also have a general data type called Menu Item. For each Menu Item you could have a Main Category and a Sub-Category field that are set to their data type to the corresponding option set.
If Burger is an option for Main Category, then Custom Burger and Specialty Burger would be valid options for Sub-Category. You can create an attribute on Sub-Category called Ingredients and set it as a list of texts so that each Sub-Category option has a list of ingredients.
For the way the patty is cooked, you could have field on the Menu Item data type called Patty Doneness. This field could be set as a data type of Doneness, an option set that defines all the ways the patty could be cooked.
I was playing with Data Type to create all items for Main Category and Sub Category, but I think your way of using Data Type → Main Category (Option Set) → Sub Category (Option Set) seems much easier! Thank you!
May I extend my question of structuring this database:
The way I was thinking:
when a customer dine in the restaurant, we start by picking a Table (Data Type or Option Set?)
1.1 the Table able to record i) number of customers; ii) the starting date and time, iii) the ending date and time when the customer check-out, and lastly the iv) Orders
after selecting the Table, we can jump into the Menu (Data Type with all the Option Sets), pick his food and store his orderings into Orders
once the customer is done with his meal, he can check out for his Payment
3.1 Payment should have i) every data from Table; ii) Sub Total; iii) Discount and Membership (if there is any to apply); iv) Final Total; v) Payment Collected; vi) Changes of the Payment
Lastly, the Table can be free out for the next customer, and the previous record can be store into a new database Sales Record for future reports
my question is this too complex and should i try to simplified it? the Payment database maybe able to included into Table? and how can I store everything from Table to Sales Record and free out Table?
