Design – Build – Run: Applied Practices and Principles for Production-Ready Software Development

17.1. Maintaining Meaningful Data

Chapter 13 defined the high-level requirements for the case study's reporting, processing, and data elements. This section discusses some of the data elements and tables you can use to improve the solution's overall output and reporting capabilities. The focus will be on the application itself, and what the application should record and maintain in the database. The reporting solution (and other external sources) can then make use of this data to provide meaningful reports.

The following represent the four primary principles for maintaining meaningful data:

• Use lookup tables.

• Record dates and times.

• Maintain transaction counts.

• Define the master data set.

17.1.1. Using Lookup Tables

It is exceptionally useful for the database to be self-referencing — that is, the data should essentially describe itself so that it is easily understood. This is equally important for reports and the information they contain. For example, consider the following abridged data sample from the EmailQueue table, which could be used as the basis for a report listing items in the queue:

EmailRequestId    AccountId    AccountStateId    EmailTypeId
------------------------------------------------------------
1                 1            1                 1
2                 2            2                 2
3                 3            3                 3

The EmailRequestId value is simply a unique identifier for each record in the table; the AccountId field is the unique account identifier associated with the e-mail request; the AccountStateId is an identifier on the account record that indicates the account ...