Results 1 to 3 of 3
  1. #1
    Join Date
    May 2008
    Posts
    33

    History of Changing Attributes

    I have a question about storing the history of particular objects in a database. For example, if I had a table of "People" which had fields "PersonId", "Name", "PhoneNumber", "Height", "Weight", "Proffession" the data in every field stored for each person can change over time, except for the "PersonId", of course, which is why it is included.

    I would like to be able to view a persons attributes at any point in time and therefore need to maintain a history. The currenct approach in place is to archive images of the whole table at certain points in time, which is unacceptable as it misses some changes, is not very accessible and also stores data which does not change.

    My solution would be to created seperate tables for each changing attribute and have corresponding date for each change. For example, for phone numbers have a table "PeoplePhoneNumbers" with fields "PersonId","PhoneNumber" and "ChangeDate". A few shortcomings I can see in this approach is that firstly there will be many tables, one for each changing attribute, which can be in far greater number than those mentioned. Secondly, joins will have to created between every attribute table to get the orignal single table form, although I don't see this as a very important issue.

    I am wondering; is there a more elegent way to structure for objects of this changing nature, or is having seperate tables for each changing attribute the best solution? I'm sure this is a very common issue. Thanks very much for the help,

    Mike

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    It's a common topic that's been argued many times.

    Here's a simple example of it working on a web page that can track all changes.
    Click "history" to see history of changes.

    Mike

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Here is a script I use to create archive tables and triggers. It generates SQL Script that you can execute, so you will need your query tool set to display the maximum number of characters to keep the output from being truncated.
    Attached Files Attached Files
    If it's not practically useful, then it's practically useless.

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

Posting Permissions

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