Orders table structure, mysql

Just simple question - how to organize an orders' table, i.e.开发者_如何学JAVA when somebody orders 2x item with id=1 and 3x item with id=2.

My previous solution were to save it like: 2x1,3x2 in products column, and then explode() it, but it's very inefficient.

---

I would go with 3 tables :

• The product table -- which is quite independant from the ordering system, and is used by the website to just display products
• An order table, which stores the basic informations of an order (like who ordered, the billing address, ...)
• And a order_product join-table between those two, indcating, for each order, which products it contains, and in which quantity.

That last table would contain, at least, the following fields :

• id_order : identifier of the order
• id_production : identifier of the product
• quantity : number of times this product has been bought in this order

---

I think the accepted answer is very good, but I would extend it with a customers table, also.

Here is a fuller example of the structure I am planning to use for my project (not tested yet)...

CUSTOMERS table:
    _id
    name
    address
    tel
    email

PRODUCTS table:
    _id
    name
    price

ORDERS table:
    _id
    customer_id
    datetime

ORDER_PRODUCTS table:
    _id
    order_id
    product_id
    product_quantity

...so, basically, if a customer has 3 different products (e.g., 1 apple, 2 bananas and 4 hats) in his order, then we would see 1 row being added to the ORDERS table and 3 rows being added to the ORDER_PRODUCTS table.

---

Here is another proposal, a bit more expanded:

customers table:

id
name 
email
timestamps  (created, modified, deleted)

customer_addresses:

id
customer_id
street
zip_code
city
state
country
type (enum or varchar indexed  - options would be 'billing', 'shipping')

orders:

id
customer_id
subtotal (without taxes, discount, shipping, etc.)
total (including additional order line items)
billing_address_id (foreign key from customer_addresses) 
shipping_address_id (foreign key from customer_addresses) 
status (paid, checkout, canceled, failed, expired...)
payment type
timestamps (created, modified, deleted)

order_items:

id
order_id
item_id
item_quantity
price 

order_line_items ( optional, where would be stored additional costs like shipping, discount, tax, etc):

id
order_id
type
amount
timestamps