I have been working on a project where I need to store lists of orders (Food in this case) in a database.
I have tried looking around for the best way of storing these sort of lists, but I wasn't able to find any method of doing it.
At the moment, I am storing the data in phpMyAdmin/SQL with the orders being stored as raw HTML to be printed. It works, but there must be a more efficient way to store these orders.
The orders look similar to this:
"item": "Burger Meal",
"notes": ["Chips", "Water"]
"item": "Chicken Parma",
"notes": ["Lemonade"] //<-- Simpler to detect number of notes?
You create an
order_items table with a row for each item in each order, and an
item_notes table with a row for each note.
CREATE TABLE order_items ( item_id INT AUTO_INCREMENT NOT NULL, name VARCHAR(32) NOT NULL, order_id INT NOT NULL, quantity INT NOT NULL, PRIMARY KEY (item_id), UNIQUE KEY (order_id, name), CONSTRAINT FOREIGN KEY (order_id) REFERENCES orders(order_id) ); CREATE TABLE item_notes ( note_id INT AUTO_INCREMENT NOT NULL, text VARCHAR(100) NOT NULL, item_id INT NOT NULL, PRIMARY KEY (note_id), UNIQUE KEY (item_id, text), CONSTRAINT FOREIGN KEY (item_id) REFERENCES order_items(item_id) );
You can then get all the information about an order with a join:
SELECT o.*, i.*, n.* FROM orders AS o LEFT JOIN order_items AS i ON o.order_id = i.order_id LEFT JOIN item_notes AS n ON i.item_id = n.item_id WHERE o.order_id = 666