History of Changes

[lespaul]

In the database design I'm working on, one of the major concerns is tracking changes and who made them. I'm trying to decide the best way to implement this tracking.

This database is being designed to replace the Platypus Database (see Thread655-606833 and Thread655-846108). We need to be able to see who updated name, address, SSN, phone, any personal information. So if I have this record:


tblDefendantMaster

DefendantID: 12515
DefendantFName: Joe
DefendantMName:
DefendantLName: Blow
Address: 123 Main St
City: Albuquerque
State: NM
HomePhone: 555-1212

My question is should I:

1. Create a duplicate table (tblPreviousPersonalInfo) and add the entire record whenever ANYTHING is changed and add fields for who changed and when. So, the record in tblDefendantMaster is copied as is to the PreviousInfo table with the name and update date, and the information in the Master file is updated with the newest information.


2. Create a table that stores the individual item that was changed. So if Joe came in and changed his phone number I would store the old number in tblPreviousInfo along with the field that was changed and who changed it and when. So I would have a record:

DefendantID: 12515
FieldChanged: HomePhone
OldInfo: 555-1212
ChangedBy: USER1
ChangedOn: 8/9/2004 9:15:25 am

3. Any time information is changed, add a new record to tblDefendantMaster referencing the last DefendantID record. For instance, if we changed the phone number:

tblDefendantMaster

DefendantID: 16805
DefendantFName: Joe
DefendantMName:
DefendantLName: Blow
Address: 123 Main St
City: Albuquerque
State: NM
HomePhone: 555-2121
ChangeReference: 12515

So I add a new record for Joe with the correct phone number and reference the "old" ID number in the "new" record. Then to get the history I would need to self join through the ChangeReference field.

Any ideas on the best way to implement this? Thanks for your suggestions!

les

 

[johnherman]

If you are using ORACLE, then the Oracle CONNECT BY command will work well for the hierarchical data.

If not, I suggest using an Effective Date field and retaining the ID. Then the Primary Key becomes the concatenation of the ID and Effective Date. If more than one change per day, use Time or a Sequence following Effective Date.

So, the before and after records.....

tblDefendantMaster

DefendantID: 12515
EffectiveDate: 1/1/2004 12:00:01 am
Sequence or Time: If needed = see above
DefendantFName: Joe
DefendantMName:
DefendantLName: Blow
Address: 123 Main St
City: Albuquerque
State: NM
HomePhone: 555-1212
ChangedBy: INITIAL ENTRY

DefendantID: 12515
EffectiveDate: 8/9/2004 9:15:25 am
Sequence or Time: If needed = see above
DefendantFName: Joe
DefendantMName:
DefendantLName: Blow
Address: 123 Main St
City: Albuquerque
State: NM
HomePhone: 555-2121
ChangedBy: USER1




-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright

 

[lespaul]

we've got an IBM AS400/iSeries database. I'll have to check if the CONNECT BY has an equivalent in the SQL400 flavor! but I like the idea of the key being the ID and the date/time. most likely there wouldn't be more than a single change in a day.

Say the defendant comes to Customer Service, updates some information and is then sent to Educational Services. When they arrive in Ed Services, the personal information won't need to be updated since they just did it downstairs.



Leslie

 

[ceh4702]

Depends on the table size. You could create a new table entry based on the old data using the date/time and have a key for the current information. You could also archive what exists in another table for all the previous changes. It is obvioius over time this makes a table quite large.

If you do not like my post feel free to point out your opinion or my errors.

 

[willir]

Leslie

When I did auditing in the past, we...
- Developed an audit routine akin to an error routine
- Routine would capture EndUserName, Date, TableName(s), NewValue and OldValue (This was with Informix so we used the grunt method. In Access, you can use the DIRTY property)
- We then wrote out the change in a simple text file. I suppose you could write to a table - IDfield, Text or Memo field. We only viewed the changes without the intention of using the audit text as a mechanism to recover (unless manually). However, I know realize you could use a special character, for example the verticle bar, "|" to seperate fields.
- Your aforementioned approaches would work but would be limited to the tblDefendantMaster. A generic approach would allow a little more flexibility, and be more conservative on space usage.

I am sure ther are others that will tote some bigger and better whistles.

Richard