Questions about orders table for mulltiple items with multiple options

Question

I am making a new database for an online store and Am trying to figure out how to do the orders table. There are multiple products and each product has many options. I am just wondering the best way to keep track of all this. I keep coming back to a table with all the options for all the items. This seems like a bad thing though. Does anyone have any ideas on how to do this? Any help is much appreciated.

Answer 1

• Every product is stored in a row of table product
• Every product has zero or N options ( 0 N )
• Every order contain one or N products ( 0 N )

You need 4 tables to keep track for all this : options, products , products_orders , orders

Answer 2

I'd start with the table structure below as a base. You will almost definately need more columns than the ones i have included but it seems to me that you need what is below at a minimum.

Order
-----
   orderId
   someOtherColumn

OrderProducts
-------------
   orderId
   ProductId

product
--------
   productId
   productName


productOption
-------------
   productId
   OptionId

Option
--------
   OptionId
   OptionName