How to store the purchase price in the online store?

Question

Database design

There are tables "Goods" and "Supplies". It is planned to upload a report with a margin ( Retail price minus purchase price ).

Question: how to implement the storage of purchase price data, if it can constantly change? Let's say I can get retail price data from a completed order, but how do I get the purchase price? Where to store it in such a situation?

Answer 1

Elementary. If some attribute can change value over time, then it takes the form of tabular data.
Table "Price":
id_cost - price identifier;
id_cost_next - next price;
id_good - product identifier (foreign key of the "Products" table);
id_delivery - delivery identifier (foreign key of the "Deliveries" table);
id_type - price type: retail/purchase (foreign key of the "Price types" table);
date_set - price setting date/time;
id_currency - currency identifier: r. rub/euro/dollar (foreign key of the "Currency" table).
value - price value.
The current price of the product will be determined by id_cost_next is null, retrospective - by id_cost_next is not null.
The earliest set price for a product will be determined by min(id_cost) within the current product.