Schema Design - wizbeck/galley-grub-backend GitHub Wiki

Initial Diagram

Backend Design

The above diagram displays my initial database schema ideas for building out a POS (Point of Sale) ordering system to handle orders, items, and toppings. Some of my initial questions when brainstorming and my solution to said questions/problems:

Tables

Orders

• metadata of all items:
• has a subtotal, which will be pre-calculated in browser via items available in UI
• has a customer name
• tax is a stretch feature (use browser/device's current location to find sales tax via 3rd party API, to calculate tax for user)

Items

• name
• category: tells us which section to render item in for selection in browser
• price
• on_menu/active: boolean to gather the active items then group them by category and render them in each place respectively

Toppings

• name
• menu_type: this will allow adding an item_topping to a certain order_item to dictate additional pricing and limiting what toppings can go on certain items
• possibly add foreign key for :item

ItemToppings join table to connect toppings to items without directly relating to the item or topping record

OrderItems join table connecting items to orders, similar to item toppings, connecting items to orders through joins

OrderItemToppings this one is trickier to explain, but my reasoning is, order items that have been created will have their own set of toppings base on the menu_type or category attribute, which will be selected for each instance of order item.

The data structure I wish to achieve per single order:

# order at the top level
order: {
  *order_attributes
  # order_items nested in order
  order_items: {
    order_item_1: {
      item_id,
      order_id,
      # item_toppings per order_item nested
      order_item_toppings: {
        # order_item_toppings nested here
        order_item_topping_1: {
          *order_item_topping_attributes
         },
      }
    }
  }
}