i've trouble applying policy based, time based and customer based prices for the SKUs (Stock keeping models). To date i've produced following table for time based prices
SKUPrices ID Pk SKUID FK_To_Sku DateFrom DateTo Price
In my opinion this schema are designed for time based prices well (i want community review onto it though) but i am unable to learn how to handle customer based prices (to sell couple of skus with a clients at subsidized rates for a while period) and policy based prices (where subsidized rates will be relevant to skus if offered under some insurance policy for a while period).
An easy method to implement the above mentioned needs is always to just add 2 more posts such as this:
SKUPrices ------------------ ID Pk SKUID FK_To_Sku CustomerId FK_to_Customer PolicyId FK_to_Policy DateFrom DateTo Price
Then you may just provide default values for customer and policy ids (
0), if you wish to enter date based values. Add the client or policy FKs, if you wish to store customer or policy specific prices.
It is a fairly simple cost storing schema, however it may suffice to your requirements. You could also wish to take proper care of cost scales (different prices at different amounts) or any other needs. You may can already address this using your guidelines, I'm not sure.
Setup separate tables for Customer and Policy-based prices:
CustomerPrices ------------------ ID Pk SKUID FK_To_Sku CustomerId FK_to_Customer DateFrom DateTo Price PolicyPrices ------------------ ID Pk SKUID FK_To_Sku PolicyId FK_to_Policy DateFrom DateTo Price
Include inspections around the customer and policy-prices tables when calculating prices by separate left outer joins to every table.