Implementing complex pricing schemes using a Nested Set Model

To accomplish the proposed pricing scheme which would allow for arbitrary multiplication of any given factor by a detirmined index, the model has to be adjusted accordingly.


mysql> describe nested_set;
+----------------+---------+------+-----+---------+----------------+
| Field          | Type    | Null | Key | Default | Extra          |
+----------------+---------+------+-----+---------+----------------+
| id             | int(11) | NO   | PRI | NULL    | auto_increment |
| name           | text    | YES  |     | NULL    |                |
| additive       | int(11) | YES  |     | 0       |                |
| multiplicative | int(11) | YES  |     | 1       |                |
| lft            | int(11) | NO   |     | NULL    |                |
| rgt            | int(11) | NO   |     | NULL    |                |
+----------------+---------+------+-----+---------+----------------+

As evidenced, I droped the value field and added a couple more, namely: additive and multiplicative. This way, the pricing view is now described as follows:

CREATE VIEW `pricing` AS select `node`.`id` AS `id`,`node`.`name` AS `name`,sum((`children`.`multiplicative` * `children`.`additive`)) AS `price` from (`nested_set` `node` join `nested_set` `children`) where (`children`.`lft` between `node`.`lft` and `node`.`rgt`) group by `node`.`name` order by `node`.`id`

However, it makes sense to have an independent factor entity given that one single factor may apply to several diferent node values. The following modifications make this feasible:


mysql> describe factor;
+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| id    | int(11) | NO   | PRI | NULL    | auto_increment |
| name  | text    | YES  |     | NULL    |                |
| value | int(11) | NO   |     | 1       |                |
+-------+---------+------+-----+---------+----------------+

Add caption

and the new view as follows:

CREATE VIEW `pricing` AS select `node`.`id` AS `id`,`node`.`name` AS `name`,sum((`factor`.`value` * `children`.`additive`)) AS `price` from (`nested_set` `node` join (`nested_set` `children` join `factor` on((`factor`.`id` = `children`.`multiplicative`)))) where (`children`.`lft` between `node`.`lft` and `node`.`rgt`) group by `node`.`name` order by `node`.`id`

With this, I am able to have this sort of formulas implemented on a tree structure:

Node_Price = Sum(1,N) [ Child1 * Factor_for_child1 + Child2 * Factor_for_Child2 + ... ChildN * Factor_for_ChildN)