I am currently playing with a database which has a table in it that stores products which consists of a product id, product name and product description.

However, some of the products may have options within themselves. If for example, a product which is a Jacket is available in small at £10, medium at £12 or large at £14, rather than creating a new record for each of the 3 possiblities, is there any way that there need only be one record for the jacket, and then the 3 options with their prices stored seperately in the database?

I realise there is the possibility of storing the 3 options in another table and referencing that. But there may be other products with totally different options that are not like size but may be colours for example.

Would another table need to be created for a different product options if that were the case?

example: (I would like the below stored as 2 records in the products table with all options available to be called - a drop down menu for the options in the shoppers interface would be good - rather than 6 different records)

Product name Option Price
Men's Jacket Small £10
Men's Jacket Medium £12
Men's Jacket Large £14
Men's Hat Red £5
Men's Hat Blue £5
Men's Hat Gold £7

Generally, what would be the best way to design the table structure and implement all this?

Thanks

Originally Posted by gavrd1

rather than creating a new record for each of the 3 possiblities, is there any way that there need only be one record for the jacket, and then the 3 options with their prices stored seperately in the database?

It's possible, but you really don't want to. At some point, there'll be a product with four options, then you're SOL. So you could set it up with four options from the get go, but eventually you'll need five. You can see where I'm going with this.

What you probably want to do is create a second table, for ONE option, with the price, what makes it unique, and the ID of the product it falls under. And then allow a one-to-many relationship between these two tables. With this approach, you can pull a type of product out of the main table, and then easily drill down to sub-products that have the same ID and give a list of them, whether it's 3 or 30.

There are other options, but that's probably the best, all around.