How to record prices for the product sales?

Question

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

Answer 1

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 called SalesOrder) and the other table is the SalesLine. SalesHeader has a one-to-many foreign key relationship to SalesLine on the OrderId.

The SalesHeader stores basic information like OrderId (as the primary key) and SaleDate, where the SalesLine table has a new record for each ProductId with Quantity and Price columns, and its own primary key column called LineId.

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 and SalesLine 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. :)

Answer 2

Your problem is with the semantic meaning of the SALES table. It looks like you expect it to contain one row that represents the total sales on a given day for a given product.

It seems to me like a better definition is for the SALES table to record individual sales during the day. Then it makes sense to extend it with the details of that sale, including the unit price of the product for that specific sale. You then will also want to extend the SALES table with some reference to the individual order, or customer reference or both. You also probably want to include the exact time of sale, either as a separate column, or (simpler) by making saledate into a full timestamp.