Storing variable products options in MySQL

Question

I’m building part of a website where products can have various options that affect the price. Think bedding. There’s options for size, whether a headboard is required, and so on. Each option affects the cost. However, not all products will have all options.

For example, a 90cm bed with a headboard will have a different cost to a 120cm bed with no headboard.

I’ve worked with product options where there may be one option (such as t-shirt size or colour), but not complex options like this. What would be the best way to store such options in my MySQL database?

Answer 1

This is the design I thought of and it's an assumption without seeing your schema. Hope it clarifies my comment. If this is different from what you require, please let us know. Happy to help. :)

Reference

* SQLFIDDLE

Options Table:

ID  NAME              ATTRIBUTE     COST
1   headboard         S             55.5
2   headboard         L             65.2
3   headboard         M             60.3
4   colour_change    (null)         20.3
5   polishing        (null)         70.2

Products Table:

ID      NAME      PCOST
1001    chair     50
1002    bed1      1200
1003    table     200
1004    cupboard  2000
1005    bed2      1000

Custom Table:

ID  PID     OID     CID
1   1002    3      (null)
2   1002    4      2
3   1003    5      (null)
4   1001    4      1
5   1004    5      (null)

Query 1 to get total sum of Options used for customization per product.

select c.pid, sum(o.cost)
from custom c
left join options o
on c.oid = o.id
group by c.pid
;

Results 1:

PID     SUM(O.COST)
1001    20.3
1002    80.6
1003    70.2
1004    70.2

Query 2 : Break down by product for options cost, product cost

-- prod cost, option cost by options and prod
select x.pid, p.name, p.pcost, x.optCost,
x.optChoices
from prod p
right join (
select c.pid, sum(o.cost) as optCost,
  group_concat(o.name, ' ') optChoices
from custom c
left join options o
on c.oid = o.id
group by c.pid) as x
on x.pid = p.id
;

Results 2:

PID     NAME         PCOST  OPTCOST     OPTCHOICES
1001    chair        50     20.3        colour_change
1002    bed1         1200   80.6        headboard ,colour_change
1003    table        200    70.2        polishing
1004    cupboard     2000   70.2        polishing

Query 3: Final answer

-- total cost
select x.pid, p.name, x.optChoices,
(p.pcost + x.optCost) totalCost
from prod p
right join (
select c.pid, sum(o.cost) as optCost,
  group_concat(o.name, ' ') optChoices
from custom c
left join options o
on c.oid = o.id
group by c.pid) as x
on x.pid = p.id
;

Results 3:

PID     NAME       OPTCHOICES                   TOTALCOST
1001    chair      colour_change                70.3
1002    bed1       headboard ,colour_change     1280.6
1003    table      polishing                    270.2
1004    cupboard   polishing                    2070.2

Answer 2

You can create two tables:

1) Product : id, name, desc, cost

2) Option_master : optionId, option_name, option_unit
3) product_option_mapping : pom_id, optionid, productid

Say you have two options for a product:

The product table will have :

id     name           desc        cost
1      Product1   Product1_desc    100

option_master table will have ::

optionId    option_name      option_unit    
1              size                cm     
2             headboard          null

and the product_option_mapping will have the mapping of the product and the option