Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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);
share|improve this question
    
are your categories subject to change? are there a limited, constant number of different 'formulas'? –  Jack Douglas Oct 3 '12 at 19:24
    
categories will be subject to change however I should be able to apply same formula to many categories if I wanted to. If the formula change - I would create a new formula record and then update on existing categories records. –  I'll-Be-Back Oct 3 '12 at 19:33
    
sounds like formulas are relatively static - @grid's answer looks good to me: "you can always create an additional view that merges the two views back together" –  Jack Douglas Oct 3 '12 at 19:36
add comment

2 Answers

up vote 3 down vote accepted

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.

share|improve this answer
    
Thanks for the answer. Is there alternative way to keep a list of formula records in a category_formula table for example? I am not sure what fields do I need but you could answer that. I would use PHP to calculate the sum. –  I'll-Be-Back Oct 3 '12 at 19:45
    
For example Admin on the websute can select a formula setting on each category and save it. The formula setting could be saved in category_formula table but how the table should be designed. –  I'll-Be-Back Oct 3 '12 at 19:48
    
Are you trying to say you want a table of formulas that you use eval() in php with? I would just perform a join with formula table then perform a eval. Are you looking for something similar to this post? –  grid Oct 3 '12 at 19:56
    
some pretty crucial information was posted in chat –  Jack Douglas Oct 3 '12 at 20:01
    
"formula are subject to change every month or so."! –  Jack Douglas Oct 3 '12 at 20:02
show 3 more comments

Seems like the category table just needs an additional column, e.g. formula. In there for each id you would store the formula and be able to update it monthly, daily, whatever.

Then you need a stored procedure that constructs a dynamic SQL statement. This is pseudo-code based on SQL Server syntax (Transact-SQL) since I have no idea how dynamic SQL, CASE etc. work in MySQL. If someone wants to edit the post to make the syntax compliant, please go nuts.

DECLARE @sql NVARCHAR(MAX) = N'SELECT id, name, cost, commission, bonus,
  CASE category_id ';

SELECT @sql = @sql + '
  WHEN ' + CONVERT(VARCHAR(12), id) + ' THEN ' + formula
FROM categories;

SELECT @sql = @sql + ' END AS Value
  FROM items;';

EXEC sp_executesql @sql;

Again, sorry the syntax is for SQL Server, but I'm sure the translation to MySQL will not be overly cumbersome.

If you want the user to be able to apply any pre-existing formula to a category (or to multiple categories), it's only a little bit more complex. A new table called potential_formulas with formula_id and formula. The column in categories becomes formula_id and the query above becomes:

DECLARE @sql NVARCHAR(MAX) = N'SELECT id, name, cost, commission, bonus,
  CASE category_id ';

SELECT @sql = @sql + '
  WHEN ' + CONVERT(VARCHAR(12), c.id) + ' THEN ' + f.formula
FROM categories AS c
INNER JOIN potential_formulas AS f
ON c.formula_id = f.formula_id;

SELECT @sql = @sql + ' END AS Value
  FROM items;';

EXEC sp_executesql @sql;
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.