Multiple items in a single MySQL row?

Question

Using MySQL + PHP, I want to store several food_items for a single restaurant order in a MySQL database.

I have two tables: orders & food_items:

Many food_item_ids will be stored for a single order.

I'm just wondering, what's the best approach to storing the food_item_ids in the orders_table?

Should I store each food_item_id in a separate row of the orders table, or place all of a particular order's food_item_ids into a single row?

Answer 1

If you want 3NF (and you should unless and until you find performance is a problem), you should either:

• store the order ID in each row of the food_item table (if food_items is a per-order table);
• or use a many-to-many relationship (if food_items is just the possible things you can order).

With the first option, something like this would suffice:

Orders:
    order_id
    other stuff
FoodItems:
    order_id
    item_id
    other stuff.

For the second option, a separate table provides the many-to-many relationship:

Orders:
    order_id
    other stuff
FoodItems:
    item_id
    other stuff.
FoodItemsInOrder:
    order_id
    item_id
    count

In my opinion, all database tables should be designed in third-normal form. Once you table gets so big that performance may become an issue (and it will have to be very big), then you can start thinking about de-normalizing for speed.

For a restaurant, I cannot imagine the order and food item tables getting anywhere near big enough to warrant de-normalizing.

Answer 2

If each food item is unique to a particular order, then you should store the order ID in the food_items table, then you could query like this:

SELECT orders.id, food.id
FROM orders
INNER JOIN food ON orders.id = food.order_id

However if you have standard food items, e.g. "burger", "chips", "hot dog" then you should use three tables. One for orders, one for food items, and one to join the two:

SELECT orders.id, food.id
FROM orders
INNER JOIN orders_food ON orders.id = orders_food.order_id
INNER JOIN food ON orders_food.food_id = food.id