title: How to model a relational database that stores order details?
body: <p>I am making a restaurant POS app for android and I am trying to decide the best way to model the database for it using Room ORM that ensures maintainability. My database needs, among a lot of other things, to keep a record of all items sold within a transaction/order, as well as a log of the orders themselves and a list of the food products sold within the restaurant.
  Considering the following tables (for brevity purposes I only include columns I think relevant to the question and may not illustrate all the information I will need to catalog), I can create a table that includes a log of all the orders ever placed and call it all_orders:</p>
  
  <pre><code>all_orders
  -----------
  id (PK)
  oder_details_id (FK) - referencing the PK from order_details table
  date
  notes
  total
  payment_type
  </code></pre>
  
  <p>I can also create a table that contains all the food products/dishes that the restaurant serves, and we’ll call it all_items:</p>
  
  <pre><code>all_items
  ---------
  id (PK)
  name
  category
  price
  </code></pre>
  
  <p>No problems there so far, but my current confusion lies here—<strong>how do I manage to keep a log of the actual food items sold within an order?</strong> 
  One approach I thought about was to create a table per order number, but creating tables dynamically is already a problem and having 60,000 tables at the end of the year will be a maintainability nightmare. 
  So my other possible solution is to create a table called order_details that will probably end up with hundreds of thousands of entries per year with the following columns:</p>
  
  <pre><code>order_details
  -------------
  id (PK)
  item_id (FK) - referencing the PK from the all_items table
  order_id (FK) - referencing the PK from the all_orders table
  quantity_ordered
  </code></pre>
  
  <p>And when a user wants to pull up an order from say, last week, the program can use a join query that will produce the following to be displayed in the app’s UI:</p>
  
  <pre><code>order
  ---------
  id (PK)
  date (from the all_orders table)
  name (from all_items)
  category (from all_items)
  price (from all_items)
  total (from all_orders)
  payment_type (from all_orders)
  </code></pre>
  
  <p>I am afraid that the <strong>order_details table</strong> is just too broad since it will contain hundreds of thousands of entries, and querying it for entries will be sluggish. I'm sure indexing it will help, but is this the correct approach to this problem? If not, <strong>is there a better, “best practice” solution? If possible something that focuses on grouping any order and its items together without just dumping all items from all orders into one table</strong>. Any help will be most appreciated.</p>
  
  <p>Edit: This question is not a duplicate of <a href="https://stackoverflow.com/q/1276/11536446">this</a>, and while helpful, the supplied link has not provided any additional context on what I am really asking about nor is it entirely relevant to the answer I am after. I have bolded my last original paragraph since my question is really about a how I can model the above data as it isn't clear to me based on my research <strong>how to store actual order details attached to an order</strong> (many tutorials/similar questions I've come across fail short of thoroughly explaining the aforementioned).</p>