sparcut sparcut - 5 months ago 8
JSON Question

How do I store varying length lists/orders in database?

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:


  1. Burger Meal


    • Chips

    • Water


  2. Chicken Parma


    • Lemonade




So, what I thought would be good is to make a separate table to hold the orders so that it is structured a bit better but I can't figure out a viable way to deal with the varying lengths of orders. Otherwise, would be it be better to store it as JSON or an array and construct it in JS?

JSON example:

{
"order_id": 666,
"orders": [{
"item": "Burger Meal",
"notes": ["Chips", "Water"]
},
{
"item": "Chicken Parma",
"notes": ["Lemonade"] //<-- Simpler to detect number of notes?
}]
}


TL;DR: Best way to store varying length list in database?

Answer

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
Comments