3

I am building a Point of sales software with Sql Server 2008-R2. I am now confused about how to handle multiple items with prices change.

Let us say price of Pepsi is $2.0 on 10-09-2019 but today it's price is change to $2.50. and may be in coming days it's price will again change and will come back on $2.00.

So at the end of the month when i need monthly report of items. Then how i will deal with this type of situation. Either i will check it's current price or the price before 10 days or average of both the prices ?

Should i need a separate table to keep track of price changes of all items and query each item 's price for the specific date for reporting ?

What would be best approach according to experts to deal with this type of situation ?

Any guidance will be really appreciable.

Improve this question
1
  • First on foremost you need to discuss this with the domain experts for whom you produce the reports: what do they want to see in the particular report? Nevertheless it might be a good idea to keep track about the pricing independently from these requirements, in case you expect to get more requirements about the price evaluation like this.
    – Doc Brown
    Sep 22, 2019 at 7:44

4 Answers 4

Reset to default
2

You will need to properly design your database to store time-series data about pricing.

This will support you in querying how you are selling items, as you will know what your store is offering at any given time and contrast that against sales. It also lets you setup prices in advance, so that a sale will have all prices change immediately at the appointed minute (or back again at the end).


Product(ProductGuid, Name, ...)
Price(PriceGuid, ProductGuid, PricePerUnit, EffectiveFromDate, EffectiveTillDate, ...)
Receipt(ReceiptGuid, Total, ....)
ReceiptLine(ReceiptLineGuid, ReceiptGuid, PriceGuid, Amount)

And/Or...

You will need to record the product and the price it was sold for.

This is great for auditors (particularly if each Receipt/ReceiptLine record is write once). It is also great in that if tomorrow a product is removed, your receipts still maintain the monetary information.


Product(ProductGuid, Name, PricePerUnit, other Details....)
Receipt(ReceiptGuid, Total, ....)
ReceiptLine(ReceiptLineGuid, ReceiptGuid, ProductGuid, Amount, PricePerUnit, ...)
Improve this answer
3
  • In your first option I'd sort of argue that the EffectiveTillDate is redundant. To me, once a price is set, it remains in effect until the price is changed again. Plus when the entry is made, you don't know when in the future it will change again - so how do you even fill it it?
    – Peter M
    Sep 19, 2019 at 12:30
  • @PeterM Using a single date is a reasonable approach. For my use cases, I find that the implicit time series makes certain queries more complicated than using from/till datetimes. I handle this vagueness with an end-of-time value, generally max datetime. This allows me to quickly query when a price is in effect, avoids null checks, and allows a trivial validation by checking for overlaps. When I introduce a new record I must also update the others, and that is a source of complexity, and errors. In my domain that is easily mitigated and offset by having a decent audit log, and simple queries.
    – Kain0_0
    Sep 20, 2019 at 1:44
  • I feel your 'or' should be 'and'
    – Ewan
    Sep 22, 2019 at 5:56
4

You must record the price the item was sold at against each order and report against that. DONT calculate what you think the price would have been in your report.

  1. You need this for basic auditing and accounting anyway.

  2. The price the item sold at may not have been correct. Maybe the till didnt get the price update.

  3. The price calc logic can easily become more complicated than its possible to do in a report. eg buy one get one free, 10% off thursdays, discount on broken item. etc etc

  4. It makes your report accurate and super simple. select sum(price), product from orders group by product

Improve this answer
1
  • Yup - this is the only sane way to do it, and in some places may be the only legal way! (see French "NF525" rules). It's also the best way to cope with discounts including adhoc manager discretion discounts.
    – pjc50
    Sep 23, 2019 at 9:22
2

In general, a business does not sell products. It sells offers for those products.

An offer is an immutable entity that is tied to a product, and contains, among other things, a start and end date/time and a price or price structure. In addition, it may contain other attributes, such as channel (sold on the internet or in a retail storefront), taxable region/municipality, commission structure, and so forth.

Once created, the offer exists forever, although it can be marked as "not for sale" so that it can't be sold any more. Thus the process for "changing a product price" is actually "introduce a new offer with a new price." The old offer should be retained in the database, but marked as "no longer for sale," and hidden on any product pages.

It's the offer record, not the product record, that you use in your reports. Since its price never changes, your reporting problem is effectively eliminated.

Improve this answer
1

I would compose the database such that products can have differening prices based on the datetime of the order, but also with a snapshot of the price denormalized into the order line item tables for ease of reporting and also to guard against data corruption in case someone accidentally updates an older price lookup.

Product (id, name)
ProductPriceSchedule (productId, price, startDate, endDate [nullable] )
Order (id, orderDatetime) // allows for back-dating to get older price
OrderLineItem (productId, orderId, priceAtTimeOfOrder [denormalized])
Improve this answer
2
  • Interesting. But could you elaborate on what’s different compared to Kain0_0’s answer ?
    – Christophe
    Sep 19, 2019 at 17:43
  • Mine is a combination of the two techniques separated with an "Or" in Kain0_0’s answer.
    – GHP
    Sep 19, 2019 at 20:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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