database structure for multiple options on products

Question

I have products in my database (MySQL). I have my prices based on different combinations of options for my products. I have the following data:

PRODUCTS

prodID | prodName
1      | Ball

OPTIONS

optionID | optionName | prodID
1        | color      | 1
2        | size       | 1
3        | material   | 1

OPTIONVALUES

ovID | optionID | ovValue
1    | 1        | red
2    | 1        | blue
3    | 2        | small
4    | 2        | big
5    | 3        | wood
6    | 3        | glass

for prices I would like to have like this:

red small wood ball - $13 red small glass ball - 14 red big wood ball - 16 red big glass ball - 17 blue small wood ball - 12.5 [...]

How can I design the database structure for this? I have tried several ways but none was good. if the number of the options were static then every option could have its own table, but this was that is not a good solution.

Flo · Accepted Answer · 2011-12-06 11:40:02Z

1

I like what you already have. If you want it to be fully flexible as i guess you do, i would do the following:

Add two more Tables.

Product_Instance

instanceID | prodID | price
1          | 1      | 13

Product_Instance_Options

instanceID | ovID
1          | 1
1          | 3
1          | 6

In this way you could define all possible combinations and prices for them. Might be a hell of a lot work to set up all the prices, but if you cant calculate them (like glass = +2$, small=2$ / big=4$) you will have this however you do it.

answered Dec 6, 2011 at 11:40

Flo

1,6714 gold badges22 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

jx12345 Over a year ago

how would you query this to get a price for a given array of options?

jx12345 Over a year ago

answer to my comment question above: stackoverflow.com/questions/40296781/…

Iljaas · Accepted Answer · 2011-12-06 13:46:53Z

1

How about something like this:

productTypes

prodID | prodName
1      | Ball
2      | Car

productInstances

prodInstanceID | prodID | Prize
1              | 1      | $19
2              | 1      | $50
3              | 2      | $8

properties

propertyID  | propertyName
1           | color
2           | size
3           | material

propertiesValues

pvID |propertyID  | propertyName
1    | 1          | red
2    | 1          | blue
3    | 2          | small
4    | 2          | medium
5    | 2          | large
6    | 3          | wood
7    | 3          | glass

productProperties

ppID | prodInstanceID | pvID
1    | 1              | 1
2    | 1              | 3
3    | 1              | 6
4    | 2              | 2
5    | 2              | 4
6    | 2              | 7
7    | 3              | 1
8    | 3              | 3 
9    | 3              | 6

In your design you can't determine which option belongs to which product instance. If you look at your table optionvalues, there is no way to group together your options.

edited Dec 6, 2011 at 13:46

answered Dec 6, 2011 at 11:43

Iljaas

5202 silver badges12 bronze badges

4 Comments

Flo Over a year ago

you have to insert all the values (like red/small) for each and every instance of every product. You might find the word "small" hundreds or thousands of times in your Database. This is redundant = bad.

Ervin Over a year ago

anyway, +1 for a closely appropiation

Iljaas Over a year ago

@Flo, you are right about that. I modified my answer. Moved price to productinstance and created a propertyvalues table. Edit: Actually, now my answer is the same as your answer.

Flo Over a year ago

jea and now youre answer is pretty much the same as mine only with an additional primary key. (which is not neccesary)

Collectives™ on Stack Overflow

database structure for multiple options on products

2 Answers 2

2 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related