Adrian Klaver <adrian.klaver@aklaver.com> writes:
> An actual working example that shows exactly what you want to achieve would
> help. Mainly where does the base price originate?
A product has a base price (and maybe a base discount), but that may be
overridden by particular rules based on the customer, on the product type and
on the period of the year (I omitted the latter condition from my example, to
simplify).
There is a table "discounts" that for particular product, or its type, or for
a particular customer, or for a particular period, or a combination of these,
may specify either a new fixed price or a special discount.
For example, consider a product P, of type T, with a base price of 100$: I
need to be able to specify that for customer C1 the price is fixed at 90$,
while for customer C2 its price is 92$; moreover, all products of type T, in
the period from December 1st to December 24th, enjoy a discount of 10% for
everybody. It is obviously impractical to "explode" all these cases into a
flat table.
The function I mentioned is already taking all these details into account and
producing the expected results.
> In the meantime, if there is no direct relation between a product and
> customer discount I am not sure how you can avoid the above in the case you
> show above. That being generating a discount table for all products for a
> particular customer. For an order I could see the order being the relation
> that connects the customer(and their discount) to the particular products on
> the order.
The function is used to produce the listing of the products a customer *may*
buy in an online e-commerce, so the actual order has yet to come.
My doubt was about the better way to use that function from within the query
that produces the listing.
I hope this is clearer now,
thank you,
ciao, lele.
--
nickname: Lele Gaifax | Quando vivrò di quello che ho pensato ieri
real: Emanuele Gaifas | comincerò ad aver paura di chi mi copia.
lele@metapensiero.it | -- Fortunato Depero, 1929.