CODEWITHSUNDEEP
mysql
omprakash sharma
omprakash sharma

Reputation: 81

How to design table SQL table to store range and it's corresponding discount value

I wanted to create the table in the MYSQL to store the purchase ranges and it's discount values based on the purchase amount.

for example

10% for purchase of 0-1000

20% for 1001-2000

30% for 2001-3000

&

40% for 3000+ (anything more than 3000)

I have saved the first three ranges as follows, but what should be the max value of the last range?

min      max     discount
-------------------------------
   0     1000    10%
1001     2000    20%
2001     3000    30%
3000+       ?    40%

Or is there any other way to do so?

Upvotes: 1

Views: 2115

Answers (2)

Zack
Zack

Reputation: 2341

Concurring with @juergend that your discounts table should simply have the "min" amount and discount. However, I would argue it's better to store both values as NUMERIC rather than INTEGER:

+------------+----------+
| min_amount | discount |
+------------+----------+
|        0.0 |     0.10 |
|     1001.0 |     0.20 |
|     2001.0 |     0.30 |
|     3000.0 |     0.40 |
+------------+----------+

You can also create a derived table containing the range you need (e.g. as a view):

SELECT
    min_amount,
    (
        SELECT MIN(d2.min_amount) 
        FROM discounts d2 
        WHERE d2.min_amount > d1.min_amount
    ),
    discount
FROM discounts d1

will give you the range you need:

+------------+------------+----------+
| min_amount | max_amount | discount |
+------------+------------+----------+
|        0.0 | 1001.0     |     0.10 |
|     1001.0 | 2001.0     |     0.20 |
|     2001.0 | 3000.0     |     0.30 |
|     3000.0 | NULL       |     0.40 |
+------------+------------+----------+

Note that the highest value has a NULL for the max_amount.

Then, if you have a table of purchases, your query to get the discounted amount will look something like this:

SELECT
    p.purchase_amt AS original_amount,
    p.purchase_amt * (1.0 - dr.discount) AS discounted_amount,
    dr.discount
FROM purchases "p"
    LEFT JOIN discounts_range dr ON
        p.purchase_amt >= dr.min_amount
        AND (p.purchase_amt < dr.max_amount OR dr.max_amount IS NULL)

giving you the correct results:

+-----------------+-------------------+----------+
| original_amount | discounted_amount | discount |
+-----------------+-------------------+----------+
|         2500.00 |         1750.0000 |     0.30 |
|         2000.00 |         1600.0000 |     0.20 |
|         3005.00 |         1803.0000 |     0.40 |
+-----------------+-------------------+----------+

Upvotes: 3

juergen d
juergen d

Reputation: 204924

You could just store the min amount

discounts table
-----------+----------
min_amount | discount
-----------+----------
1          | 10
1001       | 20
2001       | 30
3001       | 40

Of course all columns are integer values.

To get the discount for 2500 the query would be:

select discount
from discounts
where min_amount <= 2500
order by min_amount desc
limit 1

The min_amount column should be indexed for performance reasons.

Upvotes: 1

Related Questions