Mysql – How to store formula method in the table

database-designMySQL

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');

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

--
-- Table structure for table `items`
--

CREATE TABLE IF NOT EXISTS `items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `category_id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `cost` decimal(6,2) NOT NULL,
  `commision` decimal(6,2) NOT NULL,
  `fulfilment` float NOT NULL,
  `bonus` float NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `items`
--

INSERT INTO `items` (`id`, `category_id`, `name`, `cost`, `commision`, `fulfilment`, `bonus`) VALUES
(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);

Best Answer

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.

Related Question