History tables and event logging

Besides holding the data that is necessary to support the primary business purpose of the system under construction, the DB is also a possible location to record information that is useful primarily for internal technical purposes, such as adminstration and maintenance of the system itself.

History tables

In a production system, you may desire to preserve the history of changes to the data in the live database. This can be achieved through the use of history (or backup) tables, and the appropriate INSERT, DELETE, and UPDATE triggers.

Each table in the DB should have a history table, mirroring the entire history of the primary table. If entries in the primary table are to be updated, the old contents of the record are first copied to the history table before the update is made. In the same way, deleted records in the primary table are copied to the history table before being deleted from the primary one. The history tables always have the name of the corresponding primary one, but with _Hist appended.

Entries to the history table are always appended at the end. The history table, therefore, grows strictly monotonically in time. It will become necessary to periodically spool ancient records to tape for archiving. Such records may, as a result, not be immediately available for recall.

The attributes of the history table should agree exactly with the attributes of the primary table. In addition, the history table records the date and type of the change to the primary table. The type is one of the following: Create, Update, or Delete.

Changes to the structure of the primary table affect the history table. When an attribute is added to the primary table, it is added to the history table as well. When an attribute is deleted from the primary table, the corresponding attribute is not deleted from the history table. Instead, this field is left blank (NULL) in all future records. Consequentially, the history table not only grows in length over time, but also in width.

Note that the choice to use such a history mechanism affects neither the overall DB layout, nor applications that access only the primary tables. During development, you can probably dispense with recording changes in this way and leave the creation of the history tables and the necessary triggers until installation time.

Event logging for fun and profit

A database can be used as an event logger. The notion of event is broad, ranging from common debugging and system specific runtime information, to events which are specific to the business domain. Possible candidates for events to be logged to the database include:

• Transactions making changes to persistent data
• Transactions crossing component boundaries
• Errors and exceptions
• Dispatching of messages to the user
• Events involving financial transactions
• State changes to business entities

An EventLog table to log such information contains at least these fields to record:

• Timestamp
• EventType (a type code)
• Details (a descriptive string)

Optionally, it may identify an owner or originator of the event. The owner concept can either identify a logged-in user or admin, but it may as well describe a part or module of the system itself. In applications dealing with financial transactions, additional (optional) fields identifying the from- and to-accounts can be useful.

System config tables

Finally, it is possible to use the database as centralized storage for configurational data. Usually this information is kept distributed in miscellaneous plain-text files, such as start-up scripts or property files. The database can provide a single, managed storage facility for such information.

Besides start-up parameters, which are usually supplied to the system at boot-time, one may also think of properties that are required at runtime, such as localized strings and messages.

Lastly, the database is a possible place to keep system documentation. This is most useful, of course, for information that is naturally in tabular form (rather than free text), such as lists of assigned port numbers or shared memory keys, for instance. But this approach is not limited to codes. A data dictionary, defining the permissible values for each field, is a necessity on any non-trivial project. This also can be made accessible to all developers and administrators by storing it in the database.

In any case, the data is stored in simple key/value pairs. Additional table attributes can contain comments or pointers (URLs) to relevant offline documentation.

The primary advantage to keeping such information in the database is that the database provides a central repository for all relevant information, as opposed to the typical approach in which data is scattered over miscellaneous files.