Orders table structure, mysql

Question

Just simple question - how to organize an orders' table, i.e. 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.

Answer 1

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