I have an food ordering system that has main item such as latte, long black, flat white, espresso, americano etc. Each item is stored in a DB as follows.
*example missing unnecessary columns
Code:
+----+----------------+-------------+
| id | item_name | price |
+----+----------------+-------------+
| 1 | latte | 2.0000 |
+----+----------------+-------------+
| 2 | long black | 2.5000 |
+----+----------------+-------------+
| 3 | flat white | 3.0000 |
+----+----------------+-------------+
| 4 | americano | 2.0000 |
+----+----------------+-------------+
I need to add sub-options to each item but each item will have unique set of options for example americano will have a milk type, sugar type. Flat white will have a milk type also but will need to be a different menu as americano may not require a milk option so will have a different submenu. For example.
Flat white milk menu
Code:
+----+----------------+------------+
| id | item_name | submenu_id |
+----+----------------+------------+
| 1 | soy | 1 |
+----+----------------+------------+
| 2 | rice | 1 |
+----+----------------+------------+
| 3 | regular | 1 |
+----+----------------+------------+
| 4 | trim | 1 |
+----+----------------+------------+
americano milk menu
Code:
+----+----------------+------------+
| id | item_name | submenu_id |
+----+----------------+------------+
| 5 | soy | 2 |
+----+----------------+------------+
| 6 | rice | 2 |
+----+----------------+------------+
| 7 | regular | 2 |
+----+----------------+------------+
| 8 | trim | 2 |
+----+----------------+------------+
| 9 | no Milk | 2 |
+----+----------------+------------+
Then there may be a common item such as sugar...
sugar
Code:
+----+----------------+------------+
| id | item_name | submenu_id |
+----+----------------+------------+
| 10 | Sugar | 3 |
+----+----------------+------------+
| 11 | no Sugar | 3 |
+----+----------------+------------+
What would be the best way to store the hierarchy in the DB? For example flat white would need the values 1,3 americano will need 2,3.
Code:
Flat white hierarchy
ID3 -> SUBID1 -> SUBID3
Americano hierarchy
ID4 -> SUBID2 -> SUBID3
The same kind of system may be required for something that requires many more options. I need to be able to pull these out and load each sub option with javascript.
dBforums
>
Recommendations for storing menu hierarchy
12-07-12, 17:12
Linear Mode
