So here is a simple (I think) question. I have equipment assigned do maintenance agreements in a database with pricing information etc. However, when a contract comes to term we change the contract the equipment is assigned to - no record is kept of historical contract/equipment assignments.

In the redesign of the database I see a few possible approaches:
1. When the data is changed write it to the historical record table (either when it's input, so the historical table includes all records including the current, or when changed from one contract to another the historical data, pricing etc get put into the historical table, but in both cases the current data stays in the equipment table.
2. Have an intersection table - perhaps with a date field indicating when it was last changed. The most recent becomes the "current" value, while the older records would be considered archives. I could then either just leave it at that, or flag all earlier records with an archive bit to make queries of the data easier.

As I alluded to, handling the date in queries and whatnot would be the potential drawback to just using the intersection table, I know it could be done, but I'd have to do some additional nesting of queries (unless of course I did the archive flag thing). Still, I am leaning in this direction, but ease of use seems to contradict "good database convention," thus the notion of the archive bit which kind of meets in the middle.

Which approach would you think best?

Thanks

Most efficient way to store history

Thanks, not so mysterious. My original approach + a history table.
Thanks

So, with an unbound form, I could populate the form from a recordset rs,
me.equipid=rs.equipid
me.contractid=rs.contractid
etc.

Keep track if something has changed, and if so allow an "update or save changes button."
then
do an insert query into by history table using my rs values, set my rs values to my control values and rs.update.

yes?

Thanks

Maintain current data in your primary table. Maintain historical data (including the most recent version) in an archive table.
Dip into the archive table for all queries that need historical data, and use the primary table for queries that only need current values.
Use triggers on the primary table to add records to the archive table on insert, update, and delete.
I have a script that will generate the archive tables and triggers for you:
sqlblindman private pastebin - collaborative debugging tool

Thanks. I'll have to study that a bit, but I get the idea. Thanks again.