MariaDB's Virtual Columns

I wanted to evaluate MariaDB's virtual column and see if it could store business rules next to table data. With virtual columns this could be done and if you specify as 'virtual', would not even take up space on disk.

The imaginary use case is that there is a sales team in a company and you would like to evaluate if a particular salesperson is eligible for a bonus. In order to get a bonus, you need to sell above the average for the day and be in the top 5 amongst the salespeople.

So here is what I did:

MariaDB [test]> create table salespeople (id int unsigned not null auto_increment primary key, salesperson_id int unsigned not null , `date` datetime not null default 0, sold decimal(15,2) not null default 0, day_avg decimal(15,2) not null default 0,  above_avg char(1) as (if(sold>day_avg,'Y','N')) virtual);
Query OK, 0 rows affected (0.02 sec)

So far so good.

 MariaDB [test]> insert into salespeople(salesperson_id, `date`, sold, day_avg) values (1,now(),300,150);
Query OK, 1 row affected (0.01 sec)
MariaDB [test]> select * from salespeople;
+----+----------------+---------------------+--------+---------+-----------+
| id | salesperson_id | date                | sold   | day_avg | above_avg |
+----+----------------+---------------------+--------+---------+-----------+
|  1 |              1 | 2012-04-19 13:16:32 | 300.00 |  150.00 | Y         |
+----+----------------+---------------------+--------+---------+-----------+
1 row in set (0.00 sec)

Cool, just like mashing a view into a table.

MariaDB [test]> update salespeople set sold = 149 where id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
MariaDB [test]> select * from salespeople;
+----+----------------+---------------------+--------+---------+-----------+
| id | salesperson_id | date                | sold   | day_avg | above_avg |
+----+----------------+---------------------+--------+---------+-----------+
|  1 |              1 | 2012-04-19 13:59:57 | 149.00 |  150.00 | N         |
+----+----------------+---------------------+--------+---------+-----------+
1 row in set (0.00 sec)

Works again. No triggers for UPDATE needed.
Now lets try to add another business rule:

MariaDB [test]> drop table salespeople;
Query OK, 0 rows affected (0.03 sec)

MariaDB [test]> create table salespeople (
    -> id int unsigned not null auto_increment primary key,
    -> salesperson_id int unsigned not null,
    -> `date` datetime not null default 0,
    -> sold decimal(15,2) not null default 0,
    -> day_avg decimal(15,2) not null default 0,
    -> above_avg char(1) as (if(sold>day_avg,'Y','N')) virtual,
    -> day_position int not null default 0,
    -> give_bonus char(1) as (if(above_avg='Y' and day_position between 1 and 5,'Y','N') )virtual
    -> );
ERROR 1900 (HY000): A computed column cannot be based on a computed column
MariaDB [test]>

DOH! >.<

ADDITION:

I have found a way to store more business rules with the limitation of not being able to use another virtual column to "build" a more complex rule set:

MariaDB [test]> create table salespeople (
    -> id int unsigned not null auto_increment primary key,
    -> salesperson_id int unsigned not null,
    -> `date` datetime not null default 0,
    -> sold decimal(15,2) not null default 0,
    -> day_avg decimal(15,2) not null default 0,
    -> day_position int not null default 0,
    -> bonus_amount decimal(15,2) as (
    -> case when sold>day_avg and day_position between 1 and 5
    ->  then 0.2 * (sold - day_avg) #20% bonus
    -> when sold>day_avg and day_position >5
    ->  then 0.05 * (sold - day_avg) #5% bonus
    -> else 0
    -> end )virtual
    -> );
Query OK, 0 rows affected (0.75 sec) 

MariaDB [test]> insert into salespeople(salesperson_id, `date`, sold, day_avg, day_position) values (1,curdate(),300,150,3);
Query OK, 1 row affected (0.01 sec) 

MariaDB [test]> select * from salespeople;
+----+----------------+---------------------+--------+---------+--------------+--------------+
| id | salesperson_id | date                | sold   | day_avg | day_position | bonus_amount |
+----+----------------+---------------------+--------+---------+--------------+--------------+
|  1 |              1 | 2012-04-20 00:00:00 | 300.00 |  150.00 |            3 |        30.00 |
+----+----------------+---------------------+--------+---------+--------------+--------------+
1 row in set (0.00 sec)

In the above example, the actual bonus amount was calculated.