Trying to properly setup a price list for a project - just thinking out loud...

 

Product table has 200 items. no prices only descriptions & specifications

 

The price table has the id of the product and the price currently there is also a distributor price and a volume price these are calculations that do a percentage discount off the main price.

 

However this will change and each item will be allowed to have a different percentage for the distributor / volume. 

 

When a client who is flagged as a distributor or a volume sales client they would get these prices looked in to the invoice.

 

So now I am considering adding an effective date so that we could add prices effective at some point in the future and when that day arrives pricing is reflective of that.

 

The other thing is that I believe some clients get a contracted rate for certain items - so I am not sure if I need another table for custom pricing or add the column for the customers id and then create a process to copy the current list and import it or replicate it for each client. OR use the orders - line items as the "lookup" for contracted rates if none are active or exist or with in the effective date and or expiry date it would fallback to the price list. 

 

 

 

 

 

 

 

 

 

Product table has 200 items. no prices only descriptions & specifications

 

The price table has the id of the product and the price

 

Can't you combine the two tables and eliminate the one-to-one relationship? Scratch this if you're considering adding future prices, with EffectiveDate field.

 

 

When a client who is flagged as a distributor or a volume sales client they would get these prices looked in to the invoice.

 

Is volume price a function of the client, or of quantity?

 

I believe some clients get a contracted rate for certain items - so I am not sure if I need another table for custom pricing

I believe that's exactly what you need. A join table between products and clients.