0004619: Method to log all changes to the database without generating log files

Description

Most businesses would like to log all user interaction so that when it is necessary to turn detective, it should be easy to find out who did what and therefore try to stop a repeat of the error.

My experience with clients is that they do not know what needs to be monitored until after the event by which time it is too late. Auditing and logging can generate huge amounts of data and there is always the problems of converting data into a consistent form.

However most auditing/logging systems require a system administrator to turn on specific logging because leaving logging on permanently generates too much data.
Turning on logging after the event is of no use and one cannot predict when an operator is going to make a mistake.

What is needed is a neat scheme to "naturally" record all the changes to the database without recourse to special logging code.

Proposed Solution

For some time I have had the idea that the best way to log changes to the database is NOT TO DELETE ANYTHING. That means that every time we do an update to a data record, we actually create a new record with the changes and mark the old record as inactive. Each new record would carry the identity of the operator creating that record and the time-stamp of creation. To delete data the record would be marked inactive recording the operator who made it inactive.

Most of the fields needed to operate such a system are already defined in the Openbravo database. So this could be incorporated in the very core of Openbravo with no changes to the higher code levels both for the current version and future versions.

Then all we need is a reporting function that can report the changes for a particular part of the database. In fact using the average database query tool would yield the information needed.

Most peoples reaction to this idea is that it would generate too much data. However I believe, from my experience working with real businesses, that most business data is written once and read many times. It tends only to be the mistakes that are deleted and rewritten and these records are, surprise surprise, the transactions that most businesses want to log.

When things go wrong in a business environment, the classic question is "How did this happen?" to which we reply "Because XXX got changed." and the next question is "Who <expletive deleted> changed it?"

I would welcome your thoughts on this idea and whether you think it would fit into the architecture of Openbravo.

Issue History
Date Modified	Username	Field	Change
2008-08-13 13:37	andrewballantine	New Issue
2008-08-13 13:37	andrewballantine	Assigned To	=> cromero
2008-08-13 13:37	andrewballantine	sf_bug_id	0 => 2049463
2008-11-10 13:10	cromero	Assigned To	cromero => pjuvara
2008-11-16 18:41	pjuvara	Regression testing	=> No
2008-11-16 18:41	pjuvara	Relationship added	duplicate of 0003433
2008-11-16 18:41	pjuvara	Status	new => closed
2008-11-16 18:41	pjuvara	Duplicate ID	0 => 3433
2008-11-16 18:41	pjuvara	Resolution	open => duplicate

Notes
There are no notes attached to this issue.