Documentation of changes

[aschone]

How much documentation do you do when you make changes to data in the database.

I writing a UDF that will change the data in a field that is in 3 different tables. My question is in regards to documenting what was changed, when it was changed, and possibly why.

I do not have any regulation entities over me requiring me to "audit" this data change. But I am curious on how others handle this situation. Do you log the original data and the changed data in case issues occur later on? If you do log the data, do you store in a table, a file, or some other storage location? How long do you maintain change history like that? Do you log some changes but not others?

I am not talking about documenting changes to the database structure, forms, reports or other objects with in the database. More focusing on when you make a batch change to several records in a table(s).

I am asking more to start a discussion than to determine a "correct" answer.

[forskare]

Re: Documentation of changes

How much documentation do you do when you make changes to data in the database.

As someone who works with sensitive data, and since no one has provided input, here's my .02. Simply put, it depends on the need.

Log original data?

Will you have a need to refer back to the original data? How important is it to refer to original data?

If you do log the data, do you store in a table, a file, or some other storage location?

Not sure what you mean by "file" or "other source" but I see no reason why a table could not be used.

How long do you maintain change history like that?

See above.

Do you log some changes but not others?

You don't ever want to be questioned as to why you didn't log the change when you logged others. It's all or none.

kenn

[DaveM]

Re: Documentation of changes

Andrew,

Clients data and app applies here. a developement app would be different. It would have no real data and would not yet be deployed.

As a practice picked up from the old days. There is a txt file with all changes made structurally to tables and all tables are backed up to a save folder under the customer/my folder. That way, if there happens to be an irregularity, it can be brought back. All forms, reports, etc. are also copied to the save folder. This of course means all the tables and supporting files.

\save010508\

how long? 1 year in my case for data. 1-3 months for other. The txt file is for my own use to remind me what I did and why.

Has it come to pass that i/we used it? YES!

[CALocklin]

Re: Documentation of changes

Why "document data changes"? That implies that you may need to reverse them if there was a problem but often it isn't possible to reverse them because the original change messed things up so badly that reversing isn't possible. [Remember the comment in the original post: "I do not have any regulation entities over me requiring me to "audit" this data change."]

For example, someone decides to change a field for some reason and runs an operation in such a way, or has the table designed in such a way, that it also updates the 'Change Date' field in EVERY record. Reversing this is almost impossible. Of course, this example doesn't really affect the "core data" but I've seen changes that messed up the core data beyond repair also.

For me "documenting data changes" means "running a backup before making complex/unusual updates." Then if something gets messed up it's real easy and fast to restore the backup.

FWIW: I even have many old backups of my customer's data on my system and/or backup drive. I'd say that at least 3-4 times a year one of them comes in handy because the customer messed something up or forgot to do something at the appropriate time. Just this week someone said they forgot to run a sort of "inventory" report last April and the only accurate way to do it was with data from that day. I happened to have a backup from the day before (they were really lucky this time) and that was close enough in this case. HINT, HINT, HINT: Keeping only a backup of yesterday's data is NOT enough. I recommend daily backups for a week, weekly for a month, and monthly for a year. It's amazing how many times a problem is not found until a long time after it occurs. I keep telling my customers this but, as you can tell, they don't always heed my warnings.