xfvgdrthbdtyvhgscv
asked on
Strategy to track changes made to db records?
Hello all,
I have a database of people with ~ 50 fields for characteristics of each person. These fields are spread across a few different tables.
Because we need to recall every update to a person's record, there is not just one record per person. When an update is made, a new record with an update id is inserted. When a user requestes a display of a user's record, the system finds the most recent update and siaplys the info from that. or they can view a list of all updates.
Well, now someone wants that when the system displays the latest information, we identify which fields have had a change since a certain prior date. And when we list all the updates for a person, identify the changed fields in each update from the previous update.
Now, I could just do a real-time comparison each and every time a display is requested. But the complexity of the tables will make the computing necessary to do this several times greater than what is needed for the current simple displays.
Another idea is to keep a change log table.
But before diving head first into it, I wondered if this isn't something many others have faced adn there's a good old "Everyone knows you od it like this..." solution out there.
Anyone? Thanks.
I have a database of people with ~ 50 fields for characteristics of each person. These fields are spread across a few different tables.
Because we need to recall every update to a person's record, there is not just one record per person. When an update is made, a new record with an update id is inserted. When a user requestes a display of a user's record, the system finds the most recent update and siaplys the info from that. or they can view a list of all updates.
Well, now someone wants that when the system displays the latest information, we identify which fields have had a change since a certain prior date. And when we list all the updates for a person, identify the changed fields in each update from the previous update.
Now, I could just do a real-time comparison each and every time a display is requested. But the complexity of the tables will make the computing necessary to do this several times greater than what is needed for the current simple displays.
Another idea is to keep a change log table.
But before diving head first into it, I wondered if this isn't something many others have faced adn there's a good old "Everyone knows you od it like this..." solution out there.
Anyone? Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This sounds like a nice compact solution for the first requirement, "when the system displays the latest information, we identify which fields have had a change since a certain prior date".
But at first look, I don't think it will fulfill the second: "when we list all the updates for a person, identify the changed fields in each update from the previous update".
Maybe I can get the second requirement dropped.....
>>yodercm:How about creating one more table, with one row for each person, and make the fields all >>datetime fields corresponding to each of the data fields involved.
>>
>>Then when you have an update, you just put the current datetime into the new table's field >>corresponding to the data that was updated.
>>
>>To display, you can retrieve the one row for the person requested, and determine immediately which >>fields have been updated after a given date.
But at first look, I don't think it will fulfill the second: "when we list all the updates for a person, identify the changed fields in each update from the previous update".
Maybe I can get the second requirement dropped.....
>>yodercm:How about creating one more table, with one row for each person, and make the fields all >>datetime fields corresponding to each of the data fields involved.
>>
>>Then when you have an update, you just put the current datetime into the new table's field >>corresponding to the data that was updated.
>>
>>To display, you can retrieve the one row for the person requested, and determine immediately which >>fields have been updated after a given date.
ASKER
Ya, that's the obvious solution. But I had imagined some code to compare the submitted info to the last db record, identify fields that have changed, then update the change-log table with a "1" in any changed field in a new record including the time-date of the change. So I'm real curious if by "triggers on the table" you mean something more automatic than that built right into an sql table?
>>Swindle:I always use a change log table and have triggers on the table that automatically insert >>change information to the change log.
Ha ha - that's happened to me. I had to search EE for questions I recalled posing to see what my username is.
>>Question for you - if you lose your cookies in your web browser, will you ever be able to remember >>what your login name was? heheh
>>Swindle:I always use a change log table and have triggers on the table that automatically insert >>change information to the change log.
Ha ha - that's happened to me. I had to search EE for questions I recalled posing to see what my username is.
>>Question for you - if you lose your cookies in your web browser, will you ever be able to remember >>what your login name was? heheh
If by your "second requirement", you mean you need to actually display the most recent prior value and the newest, then the simple way is to store the 2nd oldest data along with the change date in the new table.
ASKER
Close - For the second requirement what I meant to say is that the system currently will, on request, list every single update to a person's profile. This is easy since each time a person's info is updated a new record gets inserted into the db. For the second requirement, they want each update to show which field has been changed from the just prior update
This is tough. If you have to list every update that was ever made, then you don't have much choice but to go through all the rows for that person. You can just compare each field to the prior update to determine if changed. What happens if a field is 'changed' to the same value? offhand, I don't see how anything could be more efficient than the simple brute force method.
ASKER
Well, I was considering a change table. It has a column for each column in all the data tables. When an update is made, the update software checks the last record, and inserts a new record in the change table with a "1" in the columns that have changed and the same update ID as the new data table record.
Then when come time to display, for the first requirement, we just sum up all the change records from date-X forward. For the secod requirement, we identify the changes for each individual update id from its corresponding chagne table record. Checking this change table has to be less work on display than brute force cross-record checking.
But the change-date table you first suggested is so attractive in its simplicity that I'm going to advocate reducing the requirements in order to avoid unnecessarily complicating things.
Then when come time to display, for the first requirement, we just sum up all the change records from date-X forward. For the secod requirement, we identify the changes for each individual update id from its corresponding chagne table record. Checking this change table has to be less work on display than brute force cross-record checking.
But the change-date table you first suggested is so attractive in its simplicity that I'm going to advocate reducing the requirements in order to avoid unnecessarily complicating things.
You could take a look at a structure where you have a personID, FieldName, and datetime column in the change table -- a many-to-one relationship usually works better this way, and give you flexibility to add more data info fields if needed. Depending on your methods of update, it might be faster, although it would take a little more database space.
Question for you - if you lose your cookies in your web browser, will you ever be able to remember what your login name was? heheh