database layout storing price packages

I'm working on an application and looking for a database schema to store product sales in a efficient manner relating to the prices. So I'm looking for a database schema and an example query to calculate the price for that month

background info:

• the product price is based on the amount of sales for that month
• An identical product can have different p开发者_StackOverflowrices (depending on price negotiation per company)
• need to be able to change prices without affecting earlier sales/invoices
• store prices without VAT, VAT can change

What I have got so far looks something like this:

sales

sale_id --PK (auto_incr)
company_id
product_type --FK on products.product_type
sale_date

products

product_id --PK (auto_incr)
company_id (company_id & product_type = Unique_key)
product_type

*price_packages*

product_id --FK on products.product_id
sales_min (total sales need to be between sales_min and sales_max)
sales_max
price
valid_from
valid_until
vat_id --FK on vat.vat_id

vat

vat_id --PK (auto_incr)
vat_percentage
valid_from
valid_until

I think that there is something wrong with the "sales_max" and "sales_min" columns. Also I'm doubting if this is the best way to store things. Some Advise is welcome. (if possible with an example query)

How would you enter new prices without affecting previous prices.

Why don't you write sale price and current VAT value directly into "sales" table when the order is done? This is historical data, so it's ok to duplicated it from products table.

If I understand correctly how your "price_packages" table is supposed to work it will be like this:

SELECT
   product_id
   , price
FROM
   price_packages
WHERE
   NOW() BETWEEN valid_from AND valid_until
   AND $sales BETWEEN sales_min AND sales_max
   AND product_id IN ($product_ids)

Replace "$sales" with number of sales for this product (either add counter column to products table or calculate it from "sales" table)

Replace "$product_ids" with products from the actual order.

Let me know if you have questions.