MySQL / PHP stores the formula

Question

Dilemma:

I have parameters that should store price data.

Each option may have a different formula to determine its value.

For example, the total cost of one option will cost * sq. feet, but another option will cost * perimeter feet, and the other option will still be a fixed cost.

These parameters are retrieved from the database by several sites belonging to my company.

Each of these sites should be able to calculate option prices in exactly the same way.

We currently have PHP code on each site that retrieves data from the database and then determines how to calculate it.

The problem with this method is that we will sometimes change the way the price is calculated, and when this happens, we need to edit the price module repository and then update it on each server that hosts one of these sites.

I am looking for the best solution.

I play with the idea of ​​storing a formula in a record of options in the database, then each site just needs to call this parameter, apply the data to the formula stored there, and then run the formula.

This can now be done using eval() , but I really don't want to do this.

Eval example to give you a clearer idea of ​​what I'm trying to do:

 $product->width = 3; $product->length = 4; $formula = $option->cost_formula; // $product->width * $product->length * $option->cost eval($formula); 

Does anyone have another solution?

Answer 1

Use functions .

Pseudo Code:

 CREATE FUNCTION calculation1 (@cost decimal(6,2), @feet int) RETURNS decimal(7,2) AS BEGIN --Other formula calculations RETURN @cost * @feet END 

Then you call the function from your query: SELECT calculation1('1.50', 15)...

Thus, you can update any changes in your function, and queries using the function will give the same result.