States and Transitions and the Circle of Life

An occurrence or instance of almost any object modeled within the database design can be seen as cycling through a set of states. At the most primal, an object proceeds through states such as: 1) being created; 2) existing unchanged; 3) being changed; 4) existing unchanged, again (similar to a return to state #2 above); then repeating through the third and second states, until one day it is 5) deleted/deactivated, or however one would phrase it, logically/physically removed. Alternative cycles may be more elaborate. A Product Order object may evolve through states like these: placed, approved, assembled, readied-to-ship, shipped, returned, received, etc. There might exist multiple paths through which objects move like currents in the ocean that send an object along varying directions. Some paths cause the creation of loops which send states back to previously experienced states.

The understanding of object states and their transitions obviously is of great importance to the solution developers because as processes are built they will need to support each state and every possible transition. Additionally, knowledge of object states and transitions is of vital importance to the data modeler because the data must be persisted across each of those states, and often the state of an object needs to be easily identifiable. A data model minimally requires a reference table, along with the varying entities that reference that table (the foreign keys tracking an individual object's status). Specific states drive variations of required attributes or combinations of those attributes that apply to one state and not another. The logical definition of the database can identify these variations through the use of supertype/subtype constructs.

On the physical side, decisions must be made regarding the implementation of the super/subtype logical configuration. The implementation can be a single table collapsing all states together and containing all possible attributes, or the implementation might occur as a table one-for-one for each subtype with the supertype related columns repeated inside each one of the subtype tables. Alternatively, the physical choice could end up as a table for each subtype and a separate table for the supertype. If one table is employed that contains everything suggested under the first implementation choice, then the determination of when to populate a column does not exist inside the database, as all the subtype columns likely would be defined to allow NULLs. Therefore, the application logic ultimately would decide when to populate a column. The organization also may choose to explicitly document the expected column population configurations with the various states in order to retain knowledge available to everyone. 

Additionally, on the physical side of the database, the state-changes are the kinds of events that likely will need auditing. Changes over time need tracking, which would involve answering questions such as: what was changed; when did it change; who changed it; and, why did they change it? Each organization may have differing specifics that are considered critical to preserve. The physical database design will have created items that are driven by such auditing needs. Special columns may be created to save key pieces, such as a time stamp for the last change. Log or shadow tables may be created explicitly to keep "before" and "after" versions of data content for an entire row.

Both logically and physically, the state and status metamorphosis that occurs for every object has an impact on a suitable database design. Properly composing a data model requires the designer to harmonize not only the individual data items, entities and their meanings, but also the lifecycle through which every thing progresses. Good data architects are as intimately familiar with these transitions as they are with any single data element.