Best way to structure pricing matrix in MySQL? -


i working on site user can:

  1. choose product (window blinds)
  2. choose color (there 3 groups of colors - a, b , c. price depends on color)
  3. 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

Popular posts from this blog

monitor web browser programmatically in Android? -

Shrink a YouTube video to responsive width -

wpf - PdfWriter.GetInstance throws System.NullReferenceException -