On the web site www.rss-to-javascript.com is this little article called "Put your trust in systems, not in genius". It's so brilliant that I just have to excerpt from it:

In 4 BC, the Gauls sacked Rome, a horde of individual warriors defeating other individual warriors. But by the end of the Second Punic War ... the Roman Army was unlike any other in the world. Other armies depended on the skill, bravery, and prowess of individual warriors and their leaders. The Romans created an army where systems, training, and supervision combined to create a world-conquering force ...

There are two common meanings for the word, "system." Both are relevant for the Roman Army and for us. A system is a number of inter-connected parts that work together. If you do something to one part of the system, other parts are affected. A system is also "the way we do things." Routine things are done the same way every time. Processes have the same steps every time.

In the Roman Army every routine thing was reduced to understandable and manageable actions. Large, complex activities like preparing camp for the night were reduced to individual actions that created a camp with the same layout and defenses every time. Tents were standardized. Eight men shared a tent. They also shared a mule and eating equipment which they were responsible for transporting. Training was the way that soldiers learned how to do all those routine things ...

The Romans understood something that we keep forgetting. We love "heroic" leaders. We worship "talent" as if it were an undifferentiated quality, applicable to any situation. And so we ignore the fact that great success comes from creating a system that allows people of average competence to combine to produce results that otherwise could only be achieved by people of great talent or genius. We ignore that fact that even the most talented individual will be hard-pressed to produce great results in a flawed system.

All text above in italics is exerpted from Put your trust in systems, not in genius

Previously we began our foray into the Rapid Application Prototype (RAP) system by totally standardizing the way tables are structured. Like the Roman army, we are now positioned to reap some enormous benefits that are far beyond what one might expect from just a little standardization. Like, for example, 100% automatic auditing of your entire application.

The RAP Database Generator: Archive Tables

After creating your database tables using the RAP preprocessor, there is an enormous amount of code that must then be generated using the RAP Database Generator. Instructions for running the Generator are included in the RAP demo package.

The Database Generator creates the following:

• "archive tables" (or "shadow tables") that archive all versions of all records that have ever existed in the current tables
• stored procedures that perform three types of writes into each table:
  • insert record
  • update record
  • delete record
• user functions that extract data from either the primary tables or the archive tables
• stored procedure wrappers for the user functions (these are called from the data layer)

For each current table whose definition was generated by the Preprocessor, the Generator creates one corresponding "archive" table. For example here is the archive table corresponding to TBadmUser that was generated in Part 2:

 -- **** AUTO-GENERATED ARCHIVE TABLE FOR 'TBadmUser' **** 
 if not object_id('TBadmUser#', 'U') is null drop table TBadmUser#
 GO
 create table TBadmUser# 
 ( 
    -- declare shadows for the current table's fields 
UserId bigint, 
LoginName varchar(20), 
Notes varchar(max), 
AuditDate datetime, 
AuditUserId bigint, 
AuditStatus char(1), 

-- define primary key 
constraint PK_TBadmUser# primary key clustered 
( 
   AuditDate asc, 
   UserId asc 
 ) 
 on [PRIMARY] 
 )
go 
-- PRIMARY KEY INDEX SHADOW (NONUNIQUE) 
create index ID_TBadmUser# 
  on TBadmUser# 
  (
    UserId 
   ) 
-- ORDINARY INDEX SHADOW (NONUNIQUE) 
create index UK_TBadmUser_LoginName# 
on TBadmUser# 
  ( 
    LoginName 
  ) 

The archive table is identical to the current-record table except:

• Its name is the current table's name followed by a "#" character (e.g. the shadow of TBadmUser is named TBadmUser#)
• Its primary key consists of two fields:
  • the current table's primary key (which is always one field)
  • plus the AuditDate field (AuditDate is one of the auditing fields that are common to all tables)
• All unique indices on the current table are replaced with corresponding non-unique indices to facilitate quick lookup.

The archive table keeps an active, retrievable history of all versions of all records that have ever existed in the corresponding current table. The current table's primary key is a synthetic key, which is guaranteed to be both unique and invariant for all time. Thus it is possible to find all the versions of a given current-table record by looking in the archive table for all records having that same value in the field corresponding to the current table's primary key.

The archive table tracks the changes to current-table records via the use of two of the "audit" fields, which are always copies of the corresponding audit fields in the corresponding record in the current table (until the corresponding current-table record is deleted). These two audit fields are:

• AuditStatus: contains a one-character code indicating the most recent operation performed on a given current-table record:
  • "I" indicates that the archive record represents the insertion of the current record: because a given current table record can be inserted only once, there is always exactly one corresponding archive table record with an AuditStatus of "I";
  • "U" indicates an update of the current record: since it is possible to update a given current-table record many times, there may be arbitrarily many corresponding archive-table records with an AuditStatus of "U".
  • "D" denotes the deletion of the current record:. because a given current-table record can be deleted only once, there is at most one archive-table version of a given current-table record that has an AuditStatus of "D".
• AuditDate contains the date on which the corresponding current record was inserted, updated, or deleted.

Except in the case of deletion (where the current record no longer exists) the audit-table record is always a copy of the corresponding record in the current table. In the case of deletion, the audit-table is identical to the deleted record at the time of deletion except that AuditDate contains the date/time at which the current record was deleted and the AuditStatus contains "D".

Here is an illustration of how the fields of the archive table are generated when a given record in the current table is modified. First, let's see a diagram of the correspondence between the current and archive tables. Here is an illustration of a current-table schema:

and below is an illustration of some sample contents of both the current and archive tables, with the current table's values in red and the archive table's values in green. The ellipsis represents the functional fields of the record (i.e. the fields that store data used by the application). Here we show what happens when we insert a record in the current table:

Note that the values in both records (red for the current table and green for the archive) are identical. Now let's update the record in the current table by changing its "payload" field from AAA to BBB:

Note that the most recent archive record (at the bottom) is identical to the current record. Also note that we are using arbitrary numbers for the AuditUserId foreign key, for the purpose of illustrating that the changes could be being performed by a number of different users. Now let's update the current record again:

Note that the most recent archive record is still identical to the current record. Now let's delete the current record:

After a deletion, the record no longer exists in the current table. Its memory is preserved, however, along with its AuditDate of deletion and its AuditStatus of "D" and the ID of the user who deleted it, in the archive table.

This example tracks the progress of auditing in just one table. All the tables in the database, without exception, operate on this same principle. Further, whenever a group of records in various tables is updated as a group, even through multiple operations (i.e. insertions, updates, deletions), all the records in that group are written with the same AuditDate. This means that when RAP retrieves records from the audit tables by date, the data is always delivered in self-consistent groups, i.e. you will not see child records pointing to nonexistent parents or sets of records containing possibly inconsistent collective data that they never actually contained when they were current.

Summary

We've seen how a small amount of standardization has given us the ability to automatically implement a rather startling feature for free: 100% auditing of all data in all tables. Imagine the cost in both design and implementation time if we had assigned this to a typical team to implement from scratch. But this is just the beginning. The additional stuff that we're going to get for free will make the auditing feature pale in comparison.

The Series

This is part of a series that examines the RAP application development system and a philosophy that believes in more standardization for both our database development and application organization.

1. Part 1 - An Introduction
2. Part 2- Database Design
3. Part 3 - Auditing
4. Part 4 - Insert/Update/Delete Routines
5. Part 5 - Fetch Routines