The original purpose of this paper was to describe how the databases could be designed to facilitate the Data Interceptors as mentioned in the Tool Design section in my Traceability design document.

However, this design is much more useful than merely as a part of Traceability. It also gives us stronger ability to audit and analyze accounts.

Design Objectives

The core principle of the database designs I am laying out in this paper is centered on the ability to extract the state of the system at any given point in time. Often, I have been asked to look into a transactional anomaly, and to determine what specifically happened. In some cases, the best answer I was able to give was only an extrapolation, since the values were rapidly fluctuating. After analyzing the various issues I have encountered, I came up with this list of objectives:

  1. Know the precise state at any given point in time.
  2. Able to analyze situations with rapidly fluctuating values.
  3. Able to recreate series of events over time.
  4. Track any edits made to the data.
  5. Determine who or what modified any data.
  6. Maintain high performance.

The resulting design I came up does cover all of these objectives. I should note that my primary focus is towards data integrity. However, I did make sure to keep performance in mind as well.

Next: Simple Table with Auditing