Historical vs Current data

[mergle]

Could someone point me in the right direction on how to statically store current pricing for a product in an invoice database, whereby future price changes would not change pricing on past/previously created invoices...?

 

[KeithG]

You would want to add a price field to the invoice table.

 

[ByteMyzer]

This is one of those times when you should not be normalizing the data.

Current transactional data, such as Customer Order records should not need to store the price; the pricing information should come from the master data. Historical transaction data, such as Invoice records, however, DO need to include the price, for tracking purposes. Otherwise, imagine trying to run YTD reports for data for which cost rollups were maintained monthly.

Simply put, store the price in your invoice records.

 

[EdFred]

I had the exact same issue with my database, where the item price was calculated based on a date when the pricing was determined vs the date of the invoice. That was abandoned a long time ago in favor of a table with the following fields:

*Invoice Number
*Invoice Product ID
Quantity
Notes
Item Price

I still have a pricing table for customers that allows me to click button which will import from the customer pricing table to the invoice items table. That way I can still have a preferred price for a customer for an item.

 

[The_Doc_Man]

ByteMyzer, don't go there.

The correct way to do this is to have a price table that includes four (or more) items:

Item identifier
Price
StartDate
EndDate

Of these four fields, the ItemID is a Dups-allowed key. If there is ANY prime key to this table, it is the compound key { [ItemID],[StartDate] }. Mental experiment: Does the rest of the table depend entirely on the proposed prime key (YES, if limited to those four fields as named) and does any non-key field exist that does NOT depend on the proposed prime key (NO). Therefore, this use is NOT denormalized.

You use this by finding the price ...

SELECT [PRICE], {put other desired fields here} FROM PRICEHIST, {put other involved tables here} WHERE
PRICEHIST.ITEMID = [{put your item ID here}] AND
[{put your date of purchase here}] BETWEEN PRICEHIST.STARTDATE AND PRICEHIST.ENDDATE ;

Note to purists: There MIGHT be a slight redundancy with regard to the fact that the end date of one price probably could be computed by finding the beginning date of the next price - except that in this case, we break the rule about storing a computable value in order to support the BETWEEN...AND... operator in the query above. But that isn't really denormalization even when we do that.

Now, to maintain the price history table, the most recent price has a start date of whenever that price went into effect and an end date of , say, 20 years in the future. When you update the prices, go back and properly terminate the price date ranges.

You might also consider doing something similar (not identical) regarding discounts that were applicable on given dates. Same concepts.

 

[GaryPanic]

just looking at this
It seems (and i might be wrong) that you D/b is flat and needs to have a little more depth

OK the guys here have given a lot of info (all valid)

Right lets start again and make some assumptions

Product 1 = Baked Beans price 30 pence (Or cents ) whatever
Product 2 = Fish Fingers (Or Sticks) price 40 pence etc


now do you need to know historically the price of these items or only the invocie amounts
so if I have 10 baked bean and 2 fish fingers 3.80 is invoiced

now an increase occurs as at ist of March
bakend beans 50 pence

Do you need to know the itemised items and there prices on a table basis or on the invocie basis
seems daft to know what the price of soemthing was prior to a date and yet no one bought any
if its per client/ustomer then what you need is a history table


this can be as complicated as required and does depend upon how you invoice - does your invoice show items or is it just a total


thinking out loud I would do a continious form select product from combo and quanity after updat total cost per item this would involve
invoice no (unique number)
customer id
product id
product qty
itemprice
totalitemprice (itemprice* qty)
don't worry about a running total
and a date would not go amiss
using .add method add these to a history table and run your invocies off this table
if the price of beans increases this has not effect on your history table until you reselct beans from your drop down quanility and then push the add button it would not change the historic values but would add a new invoice with new product price per customer

I have to be able to prove what rate I applied to a risk and may have to do variosu accounts functions after the orginal rate has been applied my history table filters back to the first invocie I did for that case and I can see what rate , where , date etc totals

If you wanted to store the total per invoice (I would be very careful here ) then that would not be too hard to do either - it might involve a third table call clientotal which would consist of clientid,invocienumber,date, total (I personally am not keen on this option) slightly more coplicated than it needs to be

as you can see everyone has there own views on what you are trying to do - if you put a sample of the d.base up everyone will look at this and possible amend what you are trying to do

I hope that this either helps or leads you into the right direction
as I siad - it depends do you need to keep traqck of the base pprice of items or just the invocied items per client
G

 

[GaryPanic]

Blimey everyones had a go at this
history table seems to be the one that most go for

simple put it copies data from now into a history table and locks it down (no edits)
and changes you do in your main table mean nothing to the history table until you copy this new data into it and it sits a 2 records

you can use append or .add (the .add method once understood is by far the better option the append can be a bit sliggish)

depends on the size of the d/b
client numbers and product numbers