Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Posts
    103

    Historical and Current data, same table or different

    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

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Oct 2003
    Posts
    103
    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

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    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
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Oct 2003
    Posts
    103
    Thanks. I'll have to study that a bit, but I get the idea. Thanks again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •