How to store formula method in the table

I am trying to figure out how to store formula method in the table (I could create category_formula table).

Each category will have different way of formula method to calculate the point and I need to assign formula method on each category. What the best way to do this?

For example:

CPU(category_id=1), to calculate the point the formula would be: (commision + fulfilment + bonus) - (cost) / 10

Hard Drive(category_id=2), to calculate the point the formula would be: (commision + cost) / 10

And formula would be different for other categories.

mysql> select * from category;
+----+------------+
| id | name       |
+----+------------+
|  1 | CPU        |
|  2 | Hard Drive |
+----+------------+

mysql> select * from items;
+----+-------------+-------------------------+--------+-----------+------------+-------+
| id | category_id | name                    | cost   | commision | fulfilment | bonus |
+----+-------------+-------------------------+--------+-----------+------------+-------+
|  1 |           1 | Intel CPU Core i7 3770K | 300.00 |     30.00 |       15.5 |    10 |
|  2 |           2 | 160GB Samsung Spinpoint |  50.00 |     15.00 |          0 |     0 |
+----+-------------+-------------------------+--------+-----------+------------+-------+
2 rows in set (0.00 sec)

Update:
I would use PHP to do the calculation. For example Admin on the website can select a formula setting on each category and save it. The formula setting could be saved in category_formula table but how category_formula table should be designed?

Create:

``
--
-- Table structure for table category
--

CREATE TABLE IF NOT EXISTS category (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(100) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Dumping data for table category
--

INSERT INTO category (id, name`) VALUES
(1, 'CPU'),
(2, 'Hard Drive');

-- -------------------------------------------------

You could create a view to select from and return the value the calculated value.

This is the SQL to create the view for the CPU formula:

CREATE VIEW cpu_value
AS

SELECT id, name, cost, commission, bonus, 
((commision + fulfilment + bonus) - (cost) / 10)) AS value
FROM items WHERE category_id = 1;

This would be the one for the HDD:

CREATE VIEW hdd_value
AS

SELECT id, name, cost, commission, bonus, 
((commision + cost) / 10)) AS value
FROM items WHERE category_id = 2;

To query them you would just do:

SELECT value FROM cpu_value WHERE id = 1;

this would perform the formula for the specific cpu with id = 1.

If you wanted to recombine them you can always create an additional view that merges the two views back together.

StackExchange Database Administrators Q#25382, answer score: 4