1

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

2

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

8
  • The way I was taught was to think of a single invoice as two different things. One table record is created for the whole invoice (Customer, date) and another record in a different table is for each line item bought (ItemID, Quantity, Price) Feb 3 '21 at 12:33
  • 1
    First of all thank you for the reply. if I understand correctly, I should to have these tables: SalesHeader: OrderId (PK, autoincrement), saleDate (DATE) SalesLine: LineId, ProductId, Quantity, Price every time a product is sold, I should to add a new line to SalesHeader with the date, and a new line to SalesLine with infos. I have a question: every time I sell a product (for example "Apples") in the same date, in the SalesLine table should I to increment the quantity (of "Apples") or just to add a new line?
    – secon25
    Feb 6 '21 at 16:49
  • 1
    @secon25 Yes to your first question, whenever a sale is made, that sale is recorded in the SalesHeader table and the details of that sales will be an individual line for each item in the SalesLine table. Generally the way this schema works is every time you make a new sale (even if it's exactly the same date as a previous sale) it gets new entries in the SalesHeader andSalesLine tables so you have the granularity of each sale but you can always GROUP BY the date later on to aggregate the quantities of all sales for a given date. For your second question, this is the standard for...
    – J.D.
    Feb 6 '21 at 17:46
  • 1
    ...a system that deals with Sales. Microsoft (and I'm sure other database system companies) has an example database and this is the very schema design they use, and it's also commonplace among many ERP Systems. It gives you all the flexibility and granularity you would need to report off of it, in a normalized manner that helps reduce data redundancy and improve query efficiency.
    – J.D.
    Feb 6 '21 at 17:47
  • 1
    @secon25 Absolutely, no problem!
    – J.D.
    Feb 6 '21 at 21:11
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.