Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2012
    Posts
    1

    Recommendations for storing menu hierarchy

    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.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,566
    How many levels deep will the hierarchy be?
    If it is just two (product, sub-options), then that's not really a true "hierarchy", and is simpler to handle.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •