archiving records

[Guest]

mI'm developing an estimating database. Product unit costs get updated on a
quarterly basis, yet some project bids may sit for well beyond 3 months
before they are accepted. My thought was to archive records at some point,
but when we view an estimate, we'd like to see what the unit cost at bid time
was, not the current unit cost. Is there a way to "freeze" those records or
fields? Do I need to move into a new field that does not depend on a
relationship?
Any input is greatly appreciated!

 

[Dirk Goldgar]

mI'm developing an estimating database. Product unit costs get
updated on a quarterly basis, yet some project bids may sit for well
beyond 3 months before they are accepted. My thought was to archive
records at some point, but when we view an estimate, we'd like to see
what the unit cost at bid time was, not the current unit cost. Is
there a way to "freeze" those records or fields? Do I need to move
into a new field that does not depend on a relationship?
Any input is greatly appreciated!

There are two ways I can think of to go about this. The simplest is to
store the price at bid time as a separate field, in the "bid unit"
record. That's not really the same as storing redundant data, because
"price as bid" is really a different thing from "current price".

The second way is to keep dated price records. That is, rather than
having a unit price in the Products table, you'd have a ProductPrices
table with fields {ProductID, PriceDate, Price}. When the price of a
product changes, you don't update a record, you just add a new record to
this table, dated for when the price goes into effect. The advantage of
this design is that you can always find out (by querying the
ProductPrices table) what price was in effect as of a given date,
whether it's the current date or the date of a particular bid, for
example. The disadvantage is that your table structure is more complex,
making the mechanism for updating prices more complex, and you can't
just look in the product table to find out what the current prices are.

 

[Joseph Meehan]

mI'm developing an estimating database. Product unit costs get
updated on a quarterly basis, yet some project bids may sit for well
beyond 3 months before they are accepted. My thought was to archive
records at some point, but when we view an estimate, we'd like to see
what the unit cost at bid time was, not the current unit cost. Is
there a way to "freeze" those records or fields? Do I need to move
into a new field that does not depend on a relationship?
Any input is greatly appreciated!

I suggest you store the actual bid price with the bid information.