CODEWITHSUNDEEP
mysqldatabasedatabase-design
Roeland
Roeland

Reputation: 3858

Database structure for a complex pricing matrix

I am working on a custom eCommerce solution which requires a fairly complex pricing matrix to figure out prices. Currently orders are all made in person, so the client is using a massive excel spreadsheet to figure out prices.

Three factors dictate the price of a product:

  1. The product type (5 product types)
  2. The product size (~30 different sizes: 10x6, 20x10, etc)
  3. The product border (3 border sizes: 2,3,4)

Here are some example prices:

Currently there is no formulaic/systematic approach to how the numbers work, its just a massive spreadsheet. Does anyone have any pointers in how approach this problem from a database perspective?

Upvotes: 0

Views: 1227

Answers (1)

woodvi
woodvi

Reputation: 2026

Assuming that your table covers all deliverable type/height/width/border combinations, try five columns (six if you want an id):

id long, // optional
ProductType varchar[16], 
Height float, 
Width float,
Border float,
Price float

And then find it with

SELECT Price 
FROM tbl
WHERE ProductType = @t
AND Height = @h
AND Width = @w
AND Border = @b;

Upvotes: 2

Related Questions