CODEWITHSUNDEEP
mysqldatabase-design
MoròSwitie
MoròSwitie

Reputation: 1516

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:

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.

Upvotes: 1

Views: 866

Answers (1)

Pavel Dubinin
Pavel Dubinin

Reputation: 2430

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.

Upvotes: 1

Related Questions