How to model a relational database that stores order details?

Question

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:

all_orders
-----------
id (PK)
oder_details_id (FK) - referencing the PK from order_details table
date
notes
total
payment_type

I can also create a table that contains all the food products/dishes that the restaurant serves, and we’ll call it all_items:

all_items
---------
id (PK)
name
category
price

No problems there so far, but my current confusion lies here—how do I manage to keep a log of the actual food items sold within an order? 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:

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

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:

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)

I am afraid that the order_details table 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, 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. Any help will be most appreciated.

Edit: This question is not a duplicate of this, 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 how to store actual order details attached to an order (many tutorials/similar questions I've come across fail short of thoroughly explaining the aforementioned).

Answer 1

The all_orders table would be superfluous as that is just repeating other data and would be contrary to normalisation.

You probably want a category table rather than repeat data (i.e. normalise categories).

Likewise, you also probably want a payment_type table (again to normalise).

Creating individual tables for orders would probably just create a nightmare.

Price and total aren't they the same? Saying that totals can be derived when extracting the data so there is no need to store such information.

As such the following structure schema may be close to what you want :-

DROP TABLE IF EXISTS item;
DROP TABLE IF EXISTS category;
CREATE TABLE IF NOT EXISTS category (_id INTEGER PRIMARY KEY, category_name TEXT);

CREATE TABLE IF NOT EXISTS item (
    _id INTEGER PRIMARY KEY, 
    item_name TEXT UNIQUE, 
    category_ref INTEGER REFERENCES category(_id) ON DELETE CASCADE ON UPDATE CASCADE, 
    item_price REAL
);

DROP TABLE IF EXISTS payment_type;
CREATE TABLE IF NOT EXISTS payment_type (
    _id INTEGER PRIMARY KEY, 
    payment_type TEXT UNIQUE, 
    surcharge REAL
);

-- NOTE cannot call a table order as it is a keyword (not rea true but have to enclose the name e.g.g [order]). 
DROP TABLE IF EXISTS customer_order;
CREATE TABLE IF NOT EXISTS customer_order (
    _id INTEGER PRIMARY KEY, 
    customer_name TEXT, 
    date TEXT DEFAULT CURRENT_TIMESTAMP, 
    payment_type_ref INTEGER REFERENCES payment_type(_id) ON DELETE CASCADE ON UPDATE CASCADE
);

DROP TABLE IF EXISTS order_detail;
CREATE TABLE IF NOT EXISTS order_detail (
    customer_order_ref INTEGER REFERENCES customer_order(_id) ON DELETE CASCADE ON UPDATE CASCADE, 
    item_ref REFERENCES item(_id) ON DELETE CASCADE ON UPDATE CASCADE,
    quantity
);

Example

The following is native SQL that demonstrates the schema above :-

Part 1 adding (inserting) the data :-

INSERT INTO category (category_name) VALUES
    ('Fish'),('Beef'),('Chicken'),('Lamb'),('Sea Food')
;
INSERT INTO item (item_name, item_price, category_ref) VALUES
    ('Fish and Chips',11.30,1),
    ('Steak and Kidney Pudding',15.45,2),
    ('Lamb Chops, Mashed Potato and Gravy',17.40,3)
;

INSERT INTO payment_type (payment_type, surcharge) VALUES
    ('Master Card',0.05),('Visa',0.05),('Cash',0),('American Express',0.15)
;

INSERT INTO customer_order (customer_name, payment_type_ref) VALUES
    ('Fred',3),
    ('Mary',1),
    ('Tom',2),
    ('Jane',4)
;
INSERT INTO order_detail (customer_order_ref, item_ref, quantity) VALUES
    (1,1,2),(1,2,1), -- Fred (id 1) orders 2 Fish and Chips (id 1) and 1 Steak and Kidney (id 2)
    (2,3,10), -- Mary orders 10 Lamb chops
    (3,2,1),(3,1,1),(3,3,1), -- Tom orders 1 of each
  (4,1,1) -- Just Fish and chips for Jane   
;

Part 2 - Extracting Useful(perhaps) Data

Here's and example of what you can do with SQL which includes derived data (as suggested above) :-

SELECT 
    customer_name, 
    date, 
    group_concat(item_name) ||'('||quantity||')' AS items, 
    sum(item_price) AS total_price, 
    payment_type, 
    round(sum(item_price) * surcharge,2) AS surcharge,
    round((sum(item_price) * surcharge) + sum(item_price),2) AS total_price
    FROM customer_order
        JOIN order_detail ON customer_order._id = order_detail.customer_order_ref
        JOIN item ON order_detail.item_ref = item._id
        JOIN payment_type ON customer_order.payment_type_ref = payment_type._id
    GROUP BY customer_order._id -- Treats all data for an order as a single row allowing the use of aggregate functions on the groups e.g. sum, group_concat
;

Result