Enterprise SDS Blog

Storing History and Audit Trail in Relational Databases

Mon, 08 Mar 2010 00:00:00 Z

It is a common requirement for enterprise software solutions to store data changes history and audit information. It is a huge topic so this post will concentrate only on the concepts without discussing implementation details.

From architectural point of view there are several ways to approach such a requirement and before making the decision on the specific approach it is good to analyze the requirements and constraints:

Is it planned to actively use the data history as part of the system functionality? Or in other words – is it needed to show history data to the users, or it will be stored only to perform audits and recover data in case of user mistakes?
What is the expected volume and usage of the data? Is it frequently displayed and is it expected to have a large number of records?
Are there any plans to regularly clean the history?

Depending on the answers there are usually two main routes – storing the history in the same table (live history) or using separated table (shadow history).

Live History

With this approach the history is stored in the same table as the live data.

When performing a data modification the original record is not updated, but instead a new record is created in the same table containing the updated record. The new (most recent) record becames the live record, while all previous records form the history.

Deleted records are usually managed the same way – a new record is inserted with a flag identifying the record as deleted.

In order to provide audit trail such tables often have fields containing the last modification date and the user who has made the changes.

Pros:

Functional history – the history records can be easily used by the system functionality. For example allowing users to view and comment different versions (revisions) of the same marketing material or article.
History data can be manipulated using the same API used for the live records. Usually the API methods have additional parameters like ModificationDate or LiveRecordsOnly for data retrieval and do not require specific handling for update or delete, as the history records are normal records in the live table.
Natural history for the related records – each history or live record in the primary table might have its own set of records in the related tables.

Cons:

Complicated history cleanup – as the history records might be reffered by some live records, it is not so simple to remove the history. Cleanup by date (records older than a week or month) is also much more complicated due to the mixture of live and history records.
Relativley bad performance – the live and history records stored in the same place form a huge table with many records and no optimization strategy (history/live flags and indexes) can fully mitigate this.

Tips:

Use this approach in case of a low data volume and when the history records are expected to be actively used by the users.
Optimize the live records retrieval by adding flags for “live record” or “history record”
Avoid using linked list approach to identify a group of records (each record having a FK to the previous record), instead provide a field to easily retrive the whole list of records with a simple query – for example keep the initial record id in a field like “base id” in all consequent records, this way the whole group of records can be easily extracted by this id.

Shadow History

In this case the history records are stored in a separate table. Usually the history table mirrors the structure of the live records table and this is why the approach is referred as a shadow history.

The history table might contain only a set of the fields in the live table, or in some cases there might be a separated history table for each field. For audit trail purposes the history table might have modification date and user information.

When a record is updated in the live table, a new record containing the same data is stored in the history table. To keep the audit trail for deleted records it might be decided to add a history record with status of “deleted”.

Pros:

Good performance – the live table has small (only the required) number of records.
Allows partial history – the shadow table could have only a subset of the fields, not the entire structure of the live table.
Easy maintenance – the history for the last month/week/day can be removed simply deleting by modification date older than specified date. The history can be wiped with fast table truncation.

Cons:

Not suitable for regular usage of history records – linking records from live tables to records in the history tables is not a good practice and harms the maintainability.
Separated API has to be built in order to support the live and history records.

Tips:

Keep the shadow history table as small as possible (go for partial history whenever possible).
Duplicate the live (most recent) record in the history. This way the whole history including the most recent record could be extracted with a single call.
Use consistent table naming conventions to make it easier for database administrators to identify and clean the history tables or provide stored procedure(s) for history cleanup.

Software Developers as Testers – Myths and Reality

Tue, 20 Oct 2009 00:00:00 Z

While there is no doubt about the role and the value of the dedicated software testers, there seem to be some myths about the ability of the software developers to perform testing and their value as software testers.

During the active development periods (before the completion of a phase or iteration) the developers have to code, participate in architecture and design discussions and there is no expectation to include them in the testing activities, although in reality they perform some testing by debugging their own code and writing and executing automated unit tests.

However, once the active development is finished there is usually a stabilization phase and the product has to be throughly tested and the defects have to be fixed.

During this phase there is little sense to have software developers waiting for the tester reports and doing nothing.

Unless there are dedicated software testing teams in the company, the testers in the project teams usually form 10 – 20% of the assigned resources. It may sound a lot, but in reality for a small team of 5 the mighty 20% means 1 tester and 4 developers. Is it a good idea to have one person doing the testing and four others waiting for some defects to be found so they can do some work?

Depending on the methodology and the QA process there might be a good reason for the developers to wait the whole reports before starting the fixing as it might be needed to prioritize the defects based on their severity and the available time.

In any case the goal is to utilize the available resources, so it might be a better idea to have the software developers doing testing instead of doing nothing. Is it really a good idea? Lets look at some of the myths.

Myth 1: Developers are bad testers as they are not willing to find defects.

Sounds logical as defects mean more work for the developers, reveal their mistakes etc., but in reality everybody with enough experience in our industry knows the defects are inevitable part of every product and would not take the defects found as a personal offence.

Some developers prefer to find their own errors before somebody else reports them, so they are very careful to find potential problems with their code.

In a successful team everybody is motivated to produce the best possible quality and take some pride for the results. If some of your developers do not care about the product they develop there is no need to give you an advice, you know what you should do with them.

Another motivation for the developers to find defects while testing is the understanding of the defects severity at the each phase of the product lifecycle. Defects found during the testing have much less severity than defects found after the product is implemented and could cause serious problems and financial loss. The developers are aware of this and have the motivation to prevent such defects with the final product as they may cost their job or seriously harm their reputation.

Myth 2: Developers cannot find all defects as they have built the functionality, know how it is supposed to be used and will test it the way they understood the requirements and the design documents.

While generally true, the reality is slightly different. It is a common sense to assign developers to test functionality build by other developers in the team, but even if this is not the case, many functional tests integrate several functions as part of a single scenario and the developers often see the functionality they have implemented from a new perspective and often try different usage scenarios.

Due to the functionality integration it is also very common the same function to be executed and tested directly or indirectly as part of several scenarios performed by different testers (developers).

It is important to understand that the developers are supposed to perform as testers mainly in the stabilization phases. During the development phases the dedicated testers are responsible (on a daily basis) to check any completed functionality for discrepancies with the requirements and the expectations.

As the testers prepare testing scenarios and the testing performed during the stabilization phase is mainly based on these scenarios (with little or almost no exploratory testing), the developers practically perform as dedicated testers as they usually simply follow predefined scenarios of test steps and use predefined test data.

Myth 3: It is impossible to motivate developers to perform testing tasks.

Most of the developers hate (explicit) testing – they find it boring, low profile, not creative etc., but there is something we should not forget – software developer, designer, tester – all these are roles, but behind the roles there are humans and depending on their personality they might be willing to help each other.

Usually the first time the developers are asked to help with the testing they are rarely positive, but once they are involved and go several times through this process they start understanding the testers work and appreciate their effort. We are all different, but the majority of the people would prefer to help each other, so the four developers in our example might be willing to help the single tester in their team during the important stabilization phase.

We should not forget the motivation caused by the potentially negative consequences by defects not found by the team and revealed in the product usage. It questionable whether this is a good motivation or not, but it always works as most of the people care about their own reputation and do not want to be guilty for some serious problems.

Conclusion

Are the myths busted?

Yes and no. Or in other words – depends. It depends on the personality of the developers, on their willingness to build a great product and the management ability to motivate them.

Should we have only developers in a team and no dedicated software testers – definitely NO. The developers could help a lot with the testing, but this remains a help, it is not their primary duty and there should be testers to perform exploratory testing, prepare and execute test scenarios and perform quality control on a daily basis.