Strategy to track changes made to db records?
Posted on 2007-10-01
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.