mikekal
Member
- Local time
- Today, 09:38
- Joined
- Jun 24, 2017
- Messages
- 114
I download your example but when im change the price do nothing.Holds the past price for the productcreate Another table for the Price:
tblPricing (table)
ItemID (long) 'FK to inventory table
EffectiveDate (date) 'when this price is applicable
Price (Decimal) 'the price
if i choose product c and change the price,in new event when im choosing the product ,the price is still the oldof course it will hold the last price of the item.
that was already a history and is past.
you can only change the current price.
its ok i test the base and its okif i choose product c and change the price,in new event when im choosing the product ,the price is still the old
As Pat noted, "Price as Of" is not the same thing logically as "Price". In addition, there are multiple ways the actual price charged can differ from a list price anyway. Discounts for preferred customers, for example. You either have to account for that as a price different from list at that point, or a separate discount field. It's not exactly a violation of normalization, although it has a certain feel to it that makes you stop and think through it.As your price table enables the of the price applicable to any order because you can determine the price applicable at any date, storing the price in the order table is redundant. Having noted that, I would have probably decided to store the price in the order table to facilitate reports.
I guess you didn't read my entire explanation.storing the price in the order table is redundant.
Call me jaded but when a client tells me something like that, my spidey senses crackle. This kind of statement belongs in the "there will never be more then 3 status dates" category. As soon as I commit to this kind of design, the client decides that inflation is high this year and he wants to increase the price in six months for new orders.if say the particular business has prices which are fixed each calendar year, but differ from year to year,
That is true. However, how do you handle returns (negative values)? How do you handle non-standard pricing (something they type in instead of the book price)? How do you handle replacements (zero cost items)? All of those are handled by simply saving the sale price at the time of this particular sale. Your assumption is absolutely inviolate pricing - no flexibility at all.In your example where your pricing table comprises a series of prices with the start/end date range of applicability, the price of an order now or any time in the future can be found by searching for the order date that falls during the effectiveness range.