Change Products Prices

[mikekal]

A Way To Change Products Prices Table But Keep Pevious Priced Records.I have a table "Inventory Transactions" and a table "Inventory".I have in Inventory many products and a price for each one.The inventory Transactions when i give a price in product take that price.When im change it ,change all history.I want to change onle the new records in Inventory Transactions.​

 

[arnelgp]

create Another table for the Price:

tblPricing (table)
ItemID (long) 'FK to inventory table
EffectiveDate (date) 'when this price is applicable
Price (Decimal) 'the price

 

[jdraw]

See this thread for related info.

 

[mikekal]

Ok i will see it

 

[LarryE]

If you want a separate price for each transaction, then add a price field in the transactions table. Then enter a price for each transaction.

 

[mikekal]

create Another table for the Price:

tblPricing (table)
ItemID (long) 'FK to inventory table
EffectiveDate (date) 'when this price is applicable
Price (Decimal) 'the price

I download your example but when im change the price do nothing.Holds the past price for the product

 

[arnelgp]

of 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.

 

[mikekal]

of 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.

if i choose product c and change the price,in new event when im choosing the product ,the price is still the old

 

[mikekal]

if i choose product c and change the price,in new event when im choosing the product ,the price is still the old

its ok i test the base and its ok

 

[Cronk]

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.

 

[GPGeorge]

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.

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.

 

[mikekal]

Thank you

 

[Cronk]

Pat, if say the particular business has prices which are fixed each calendar year, but differ from year to year, then I cannot see why the price cannot be be determined by the date of the order from the pricing table (PriceID, Price, PriceYear) in any future report. Discounts and any other price adjustment would be stored in the order table as an offset to the price in the particular year of sale.

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.

 

[Cronk]

I now wish I had not used the example of yearly prices as a date range related price. I did come back to your example of a pricing table which defined for any date range, the price applicable.

Negative returns can be handled by finding the price applicable to the order in the pricing table and taking the negative. I did write in my last post #15 that price adjustments such as discounts be included in the order table.

Lastly, you seem to think that I am advocating the approach of not storing the price in the order table. See the last sentence of my first post on this thread #11