sparcut sparcut - 1 year ago 68
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 Source

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 (
    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 (
    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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download