I'm building a database for daily sales report. For example:
29/01/2021 -> 50 apples were sold
30/01/2021 -> 46 apples were sold
I have these tables:
products
- id INT AUTO_INCREMENT PRIMARY KEY
- name VARCHAR(100) NOT NULL
- price FLOAT NOT NULL
- deleted_at DATETIME NULL,
- Other stuff
sales
- saledate DATE,
- product_id INT,
- quantity INT,
- PRIMARY KEY (saledate, product_id),
- FOREIGN KEY (product_id) REFERENCES products(id),
It is working. But now I have to record the price of the product when it was sold. The problem is that the product's price can be changed during the day for example:
29/01/2021 -> 43 apples were sold (Apple price: 1$)
29/01/2021 -> 7 apples were sold (Apple price: 0.90$)
30/01/2021 -> 46 apples were sold (Apple price: 1.10$)
as you can see, on 29/01/2021 we sold 43 apples to 1$ and 7 apples to 0.90$ because the price of apples has changed. I thought to add a column to sales
table: price FLOAT NOT NULL
but then how can I to have multiple prices associated to a specific saledate?
In essence, how to change the sales
table to get output like in the example above? I thought to make price
column primary key: PRIMARY KEY (saledate, product_id, price)
but honestly I don't know if it is a clean solution and if it has a bad impact on performance. Currently the query to insert a new sale is:
INSERT INTO sales (saledate, product_id, quantity) VALUES(date_value, product_id_value, quantity_value)
ON DUPLICATE KEY UPDATE quantity = quantity + quantity_value
Thank you in advance.
PS: Sorry for my bad english
Best Answer
What you really should do (and is how a lot of similar type systems architect their schema) is have two normalized tables that store sales information.
One table is the
SalesHeader
(or sometimes just calledSalesOrder
) and the other table is theSalesLine
.SalesHeader
has a one-to-many foreign key relationship toSalesLine
on theOrderId
.The
SalesHeader
stores basic information likeOrderId
(as the primary key) andSaleDate
, where theSalesLine
table has a new record for eachProductId
withQuantity
andPrice
columns, and its own primary key column calledLineId
.This allows you the flexibility to have the same product sold on the same date at different prices, or even multiple products on the same date at different prices, and even batch different orders up (if you wanted). You can adjust the
SalesHeader
andSalesLine
tables as what's more relevant to your use case, but that's the general solution. (Also please don't mind that I use PascalCasing when naming my obejcts. :)