Auto Insert based upon value

[TapeGun007]

Is it better or are you able to auto insert a percentage based upon a value.

Example:

Tech1 sells a $1,000 referral item and gets 5% commission

Tech1 sells a $2,500 referral item and gets 7% commission instead.

I know I could code this in php no problem.  I'm just wondering if this makes sense or if it's possible just to auto insert either 5%  for under $2,500 or 7% if the sale is equal or over $2,500.  There is only 5% and 7%, no other options.  The sale amount is stored in the same table.

Edited March 15, 2019 by TapeGun007

[requinix]

For something that simple: yes, in MySQL 5.7+ you can do it using generated columns and a CASE expression, however if the logic ever needs to change then you have to do an ALTER TABLE.

The fact that you would have to alter the table definition for something so simple and arbitrary means you should not do it. The commission rate should be determined through code, then stored with the rest of the sale data.

[Barand]

Create a commission rate table with each rate and the range of values to which it applies

testsale                 testrate
+----+---------+         +----+------------+----------+------+
| id | amount  |         | id | value_from | value_to | rate |
+----+---------+         +----+------------+----------+------+
|  1 | 1000.00 |         |  1 |       1.00 |  2499.99 | 0.05 |
|  2 | 2000.00 |         |  2 |    2500.00 | 99999.99 | 0.07 |
|  3 | 2500.00 |         +----+------------+----------+------+
|  4 | 3000.00 |
+----+---------+

SELECT s.id
    , s.amount
    , r.rate
    , ROUND(s.amount * r.rate, 2) as commission
FROM testsale s
    JOIN testrate r ON s.amount BETWEEN r.value_from AND r.value_to;
    
+----+---------+------+------------+
| id | amount  | rate | commission |
+----+---------+------+------------+
|  1 | 1000.00 | 0.05 |      50.00 |
|  2 | 2000.00 | 0.05 |     100.00 |
|  3 | 2500.00 | 0.07 |     175.00 |
|  4 | 3000.00 | 0.07 |     210.00 |
+----+---------+------+------------+

The same principle can also be applied to dates, so the correct rate is applied when querying historical data

testsale                              testrate
+----+---------+------------+         +----+------------+----------+------+------------+-------------+
| id | amount  | date_sold  |         | id | value_from | value_to | rate | valid_from | valid_until |
+----+---------+------------+         +----+------------+----------+------+------------+-------------+
|  1 | 1000.00 | 2019-02-26 |         |  1 |       1.00 |  2499.99 | 0.05 | 2001-01-01 | 9999-12-31  |
|  2 | 2000.00 | 2019-02-27 |         |  2 |    2500.00 | 99999.99 | 0.07 | 2001-01-01 | 2019-02-28  |
|  3 | 2500.00 | 2019-02-28 |         |  3 |    2500.00 | 99999.99 | 0.08 | 2019-03-01 | 9999-12-31  |
|  4 | 3000.00 | 2019-03-01 |         +----+------------+----------+------+------------+-------------+
+----+---------+------------+

SELECT s.id
    , date_sold
    , s.amount
    , r.rate
    , ROUND(s.amount * r.rate, 2) as commission
FROM testsale s
    JOIN testrate r ON s.amount BETWEEN r.value_from AND r.value_to
                    AND s.date_sold BETWEEN r.valid_from AND r.valid_until
    
+----+------------+---------+------+------------+
| id | date_sold  | amount  | rate | commission |
+----+------------+---------+------+------------+
|  1 | 2019-02-26 | 1000.00 | 0.05 |      50.00 |
|  2 | 2019-02-27 | 2000.00 | 0.05 |     100.00 |
|  3 | 2019-02-28 | 2500.00 | 0.07 |     175.00 |
|  4 | 2019-03-01 | 3000.00 | 0.08 |     240.00 |
+----+------------+---------+------+------------+