Best way to structure pricing matrix in MySQL? -
i working on site user can:
- choose product (window blinds)
- choose color (there 3 groups of colors - a, b , c. price depends on color)
- enter size of blind (in cm).
when user has entered size , clicks enter, ajax call fired php script executes query, looking database , finds appropriate price.
the sizes in increments of 10cm. 100, 110, 120, 130 etc. if user writes 115, automatically choose 120 appropriate size/price (it round up).
i have made structure, want know if have ideas can optimize faster/better.
products | id | sku | name | category | 1 | 010101 | window blind 1 | blinds | 2 | 020202 | window blind 2 | blinds pricing_colors | id | group | color | 1 | | red | 2 | b | green | 3 | c | blue | 4 | d | white product_colors | id | sku | color_id | 1 | 010101 | 1 | 2 | 010101 | 2 | 2 | 020202 | 1 | 2 | 020202 | 3 pricing_matrix | id | sku | color_group | width | height | price | 1 | 010101 | | 100 | 100 | 50 | 2 | 010101 | | 110 | 100 | 55 | 3 | 010101 | | 120 | 100 | 60 | 4 | 010101 | | 100 | 110 | 53 | 5 | 010101 | | 100 | 120 | 56 | 6 | 010101 | b | 100 | 100 | 60 | 7 | 010101 | b | 110 | 100 | 65 | 8 | 010101 | b | 120 | 100 | 70 | 9 | 010101 | b | 100 | 110 | 63 | 20 | 010101 | b | 100 | 120 | 66
as can see, pricing_matrix table large. have widths , height each of 3 color groups in width/height increments.
have thought following query find appropriate price:
select price pricing_matrix height >= ... , width >= .... , sku = .... , color_group = 'a' order height,width asc
please come suggestions how can improve it! :)
Comments
Post a Comment