mysql - Database Design: how to model generic price factors of a product/service? -

i'm trying create generic data model allow particular product (indicated fk product_id in sample table below) specify 0 or more price "factors" (i define "factor" unit of price added or subtracted in order total).

so there table:

=============================== price =============================== price_id (pk) product_id (fk) label operation (enum: add, subtract) type (enum: amount, percentage) value 

a book's price might represented way:

==================================================================== price_id | product_id | label      |  operation | type       | value ====================================================================  1       | 10         | price      | add        | amount     | 20    2       | 10         | discount   | subtract   | percentage | .25    3       | 10         | sales tax  | add        | percentage | .1 

this means:

price:      $20.00 discount:  - $5.00 (25%) -------------------- sub total:  $15.00 sales tax:   $1.50 (10%) ------------------------ total:      $16.50 

a few questions:

1. is there wrong initial design?
2. what if wanted create "templates" (e.g. "general merchandise" template has "price", "discount" , "sales tax" fields; "luxury merchandise" has "price", "discount", "luxury tax" fields) - how model that?
3. the above model works if each record applies total of preceeding record. so, in example, "sales tax" applies difference of "price" , "discount". if total not computed simply? example: + b + (a + 10%) - (b - 5%). how model that?
4. also, if "percentage" type doesn't apply preceeding row (as implied question #3) , applied more 1 row? need table itemize price->price_id percentage applies to?

first of need model of price labels, simple:

price_labels  id | label    1 | price    2 | discount    3 | tax 

then modified version of sample table you've given:

products_prices price_id|product_id|label_id|divider|value    1         10        1        1      20    2         10        2        100   -25    3         10        3        100    10  

here substituted label corresponding id price_labels table foreign key. additionally, omitted type field trivial since value can positive or negative float number. added divider column enable percentage parameter. think more read way well, since (and think) "minus twenty-five percent" not 0.25 .

now expression "abstraction" part bit more complicated , there lot of solutions.

price_expressions product_id | date_from          | date_until          | expression   10       |2011-11-02 04:00:00 |2011-11-12 04:00:00  | (select divider*value                                                           products_prices                                                           product_id=%product_id%                                                              , label_id=1)*                                                         (select 1+value/divider products_prices                                                           product_id=%product_id% ,                                                           label_id=2)*                                                         (select 1+value/divider products_prices                                                           product_id=%product_id% ,                                                                                            label_id=3) 

in expression field can store complex sql statement in can replace %product_id% placeholder product_id value same row:

select replace(expression,'%product_id%',cast(product_id char))  price_expression price_expressions  product_id = 10 , date_from>=date_of_purchase  , date_until<=date_of_purchase 

there 2 possible variations of way see it:

1. you can change product_id=%product_id% , label_id=n condition price_id=n since have stored in products_prices table
2. you can use expression format e.g. %price_id_1%*%price_id_2 , perform substitutions , calculations on application level not directly in sql

hope helps.