How to store complex product/order data in MySQL?

Question

I'm working on an order system for my online shop. I have 2 tables:

1. products, storing info about products
2. orders, storing general id's & infos of customer orders.

Now I want to have a way to store complex customer orders in the database. I need something that will let me know how much of each size (S, M or L) of each product is in an order.

The tricky part is that I want to be able to add/edit/delete products (of course without affecting orders from the past), so the method should be flexible

How should I go about this?

1. a separate table for every order, with products as rows?
2. one table for all orders, with products as columns?
3. some other option?

Thanks!

Answer 1

At the very least you need:

Products (one row per product)
    ProductID
    Size

Orders (one row per order)
    OrderID

OrderDetails (one row per product per order)
    ProductID
    OrderID
    Size

Note that each 'size' is its own ProductID. You'll probably want to have yet another ID that groups products that are the same 'base' product, but in different sizes.

So if Order #1 has three products, and Order #2 has four, then OrderDetails will have seven rows:

OrderID ProductID Quantity
1       234       2
1       345       9
1       456       30
2       432       1
2       234       65
2       654       8
2       987       4