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