(Advice) - Storing price break points in database

[Adamhumbug]

I have a system that creates quotes.

Most items are simple, order 2 its 2 times the price, order 10, 10 times the price ect.

I have some items that break at certain intervals (the intervals are all different.)

Item A - price is each appart from under 500 where it is a flat £500.

1 - 499 £500
500 -999 £1.00
1,000 - 1,999 £0.90
2,000 - 2,999 £0.80
3,000 - 5,999 £0.70
6,000 - 9,999 £0.60
10,000 +£0.50

 

Item B has a similar plan but the breaks are different.

I have no idea where to start with this and am struggling to come up with a reasonable database structure that doesnt seem overkill.

Also, i have shown the price in GBP but we also have a price for USD, CAD and EUR which follows the same break points.

[ginerjm]

I would have a table that shows the max quantity for a certain price value.  Then query looking for records with their quantities > than your current order, in quantity order and limit to 1.  Now you have your price value.  Then have a table or function that converts your currency amount to what you need  

[Barand]

Something like this...

+---------------+ 
| invoice_item  |
+---------------+ 
| id            |
| prod_id       |------+
| quantity      |      |      +----------+
|---------------+      |      | price    |
                       |      +----------+
                       +-----<| prod_id  |
                              | minqty   |
                              | maxqty   |
                              | usd      |
                              | cad      |
                              | eur      |
                              +----------+

SELECT i.prod_id
     , p.product_name
     , pr.usd
     , pr.cadd
     , pr.eur
FROM invoice_item i
     JOIN product p ON i.prod_id = p.id
     JOIN price pr ON i.prod_id = pr.prod_id 
                      AND i.quantity BETWEEN pr.minqty AND pr.maxqty

 

[Adamhumbug]

1 minute ago, Barand said:

Something like this...

+---------------+ 
| invoice_item  |
+---------------+ 
| id            |
| prod_id       |------+
| quantity      |      |      +----------+
|---------------+      |      | price    |
                       |      +----------+
                       +-----<| prod_id  |
                              | minqty   |
                              | maxqty   |
                              | usd      |
                              | cad      |
                              | eur      |
                              +----------+

SELECT i.prod_id
     , p.product_name
     , pr.usd
     , pr.cadd
     , pr.eur
FROM invoice_item i
     JOIN product p ON i.prod_id = p.id
     JOIN price pr ON i.prod_id = pr.prod_id 
                      AND i.quantity BETWEEN pr.minqty AND pr.maxqty

 

That could be a winner - let me have a play with that and i will report back

 

[Barand]

I regularly do the same thing with prices that change over time (date_from -date_until) so that historical reports use the price that applied at the time of sale.

[Riphil]

Will this ensure that we only select the price that applied at the time of the sale?

[Barand]

1 hour ago, Riphil said:

Will this ensure that we only select the price that applied at the time of the sale?

If you store the dates the price was valid in the price table and match the sale date against them.