Storing created/retired dates for a large amount of different entities

Question

In the db I'm working on now almost every entity has these 4 columns:

CreatedDate
CreatedBy
RetiredDate
RetiredBy

Usually this is used for logging purposes, and for some of the entities the usefulness of knowing when it was retired is arguable (but don't tell my boss). For some other things, (like a truck) it makes more sense because a "retired" vehicle may come back into service.

Anyway, I was wondering if it's a good idea to stick this information into one table since it's repeated all over the place. If it is, anyone have a good name for it..? Simply created_retired_dates?

Answer 1

It seems that you are involved in a project that requires the creation of a temporal database. You may as well find related information by searching for the terms auditable databases and database history tables.

I deem this exceptional Stack Overflow answer as the top material with respect to these topics. In such a post, @PerformanceDBA models an auditable relational database for a very interesting business environment, and the instructiveness contained in there encompasses multiple aspects that are pertinent not only to these themes, but also to database design and practice as a whole.

Background

As you know, a database is built to retain information that is relevant to its users, and information, naturally, can change as time passes. In this way, the values contained in a specific database can suffer successive modifications and, as a consequence, go ceasing to be “current”, but these circumstances do not imply that the previous “states” of the values in question become irrelevant after having undergone their corresponding chronological updates.

In this regard, yes, there are cases in which keeping track of the updates that affect an entity over time is paramount, and there are other cases where changes should be expressly forbidden and prevented, hence the retention of entity alterations would not apply. It may seem a cliché, but these points depend on your exact informational requirements, therefore you have to analyze each particular situation thoroughly so that you can define how to proceed. Then, once “audit trail” has been determined valid and necessary, it must be implemented.

Suggested approach

1. Illustrative IDEF1X diagram

Let us take the Truck entity type as a reference since, in accordance with your specifications, it is a good example of an aspect that entails enabling temporal capabilities. In order to illustrate the approach that I am going to propose to construct said capabilities, I have prepared an IDEF1X¹ diagram that is shown in Figure 1 (and you can download it as a PDF from Dropbox, as well):

As demonstrated in the aforesaid diagram, apart from depicting the Truck and User entity types, I have included an additional one that represents the History of Truck (denominated, accordingly, TruckHistory).

The only difference between the Truck and the TruckHistory entity types is the PRIMARY KEY of the latter, since it consists of TruckNumber and a complementary property called AuditedDateTime which, of course, indicates the particular point in time when a given Truck occurrence was “audited” (or updated). It is important to note that TruckHistory.TruckNumber is defined as a FOREIGN KEY that points to Truck.TruckNumber, depicting the type of association that takes place between these two entity types.

2. Resulting expository SQL-DDL logical layout

Taking the present example to the logical level of abstraction, I have derived the following DDL statements from the IDEF1X diagram presented above:

CREATE TABLE UserProfile (
    UserId          INT      NOT NULL,
    FirstName       CHAR(30) NOT NULL,
    LastName        CHAR(30) NOT NULL,
    BirthDate       DATE     NOT NULL,
    GenderCode      CHAR(3)  NOT NULL,
    Username        CHAR(20) NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    --
    CONSTRAINT UserProfile_PK  PRIMARY KEY (UserId),
    CONSTRAINT UserProfile_AK1 UNIQUE ( -- Composite ALTERNATE KEY.
        FirstName,
        LastName,
        BirthDate,
        GenderCode
    ),
    CONSTRAINT UserProfile_AK2 UNIQUE (Username) -- ALTERNATE KEY.
);

CREATE TABLE Truck ( -- Contains the “current” versions.
    TruckNumber     INT      NOT NULL,
    OtherColumn     CHAR(10) NOT NULL,
    IsRetired       BIT      NOT NULL,
    CreatedUserId   INT      NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    --
    CONSTRAINT Truck_PK              PRIMARY KEY (TruckNumber),
    CONSTRAINT TruckToUserProfile_FK FOREIGN KEY (CreatedUserId)
        REFERENCES UserProfile (UserId)
);

CREATE TABLE TruckHistory ( -- Holds the “past” versions.
    TruckNumber     INT      NOT NULL,
    AuditedDateTime DATETIME NOT NULL,
    OtherColumn     CHAR(10) NOT NULL,
    IsRetired       BIT      NOT NULL,
    CreatedUserId   INT      NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    --
    CONSTRAINT TruckHistory_PK              PRIMARY KEY (TruckNumber, AuditedDateTime), -- Composite PRIMARY KEY.
    CONSTRAINT TruckHistoryToTruck_FK       FOREIGN KEY (TruckNumber)
        REFERENCES Truck (TruckNumber),
    CONSTRAINT TruckHistoryToUserProfile_FK FOREIGN KEY (CreatedUserId)
        REFERENCES UserProfile (UserId),
    CONSTRAINT DateSuccession_CK            CHECK       (AuditedDateTime > CreatedDateTime)
);

Sample data

Having enabled the logical design previously exposed, say that we are keeping the next two rows in the UserProfile table:

+-——————-+-—————————-+-————————-+-——————————-+-——————————-+-———————————————————————-+
| UserId | FirstName | LastName | BirthDate  | GenderCode | CreatedDateTime         |
+-——————-+-—————————-+-————————-+-——————————-+-——————————-+-———————————————————————-+
|      1 | James     | Smith    | 1985-06-30 | M          | 2013-02-12 07:32:04.000 |
+--------+-----------+----------+------------+------------+-------------------------+
|      2 | Nicole    | Johnson  | 1987-10-14 | F          | 2013-03-24 09:02:03.000 |
+--------+-----------+----------+------------+------------+-------------------------+

Then, suppose that the Truck table contains the (“current” or “present”) row that corresponds to the Truck identified by TruckNumber 1750 as displayed bellow:

+-———————————-+-———————————-+-—————————-+-—————————————-+-———————————————————————-+
| TruckNumber | OtherColumn | IsRetired | CreatedUserId | CreatedDateTime         |
+-———————————-+-———————————-+-—————————-+-—————————————-+-———————————————————————-+
|        1750 | Bar         | False     |             2 | 2015-06-30 16:58:12.000 |
+-------------+-------------+-----------+---------------+-------------------------+

And, finally, assume that the Truck identified by TruckNumber 1750 holds the TruckHistory rows that follow:

+-———————————-+-———————————————————————-+-———————————-+-—————————-+-—————————————-+-———————————————————————-+
| TruckNumber | AuditedDateTime         | OtherColumn | IsRetired | CreatedUserId | CreatedDateTime         |
+-———————————-+-———————————————————————-+-———————————-+-—————————-+-—————————————-+-———————————————————————-+    
|        1750 | 2013-12-10 17:05:01.000 | Foo         | False     |             1 | 2013-06-30 10:34:12.000 |
+-------------+-------------------------+-------------+-----------+---------------+-------------------------+
|        1750 | 2014-03-22 14:08:08.000 | Bar         | True      |             2 | 2013-12-10 17:05:01.000 |
+-------------+-------------------------+-------------+-----------+---------------+-------------------------+
|        1750 | 2014-09-14 09:45:06.000 | Bar         | False     |             2 | 2014-03-22 14:08:08.000 |
+-------------+-------------------------+-------------+-----------+---------------+-------------------------+
|        1750 | 2015-06-30 16:58:12.000 | Foo         | False     |             1 | 2014-09-14 09:45:06.000 |
+-------------+-------------------------+-------------+-----------+---------------+-------------------------+

Advantages

Thus, in the TruckHistory table, there is a time series made up of each and every one of the changes related to the Truck identified by TruckNumber 1750. One can know

• who are the Users that made such changes (via CreatedUserId);
• the exact moment in which the values associated with these changes began to be “current” or “present” (by way of CreatedDateTime); and
• the specific instant in which a certain row was modified receiving new values in the “current” Truck table (through AuditedDateTime).

I consider that this configuration is much more advantageous than maintaining only the most recent Date in which each instance of a Truck was updated along with the Identifier of the User who performed the respective update operation but, at the same time, losing every one of the previous entity “states”.

Data manipulation considerations

From a data manipulation point of view, this implies that every time that (1) a precise Truck row suffers an UPDATE, you also have to (2) INSERT into the TruckHistory table the corresponding Truck values that were “current” until the aforementioned Truck UPDATE took effect. I would decidedly carry out these operations with the aid of ACID TRANSACTIONS, so that they are either succeed or fail as a single Unit of Work.

Similar scenario

I do not know how the post revision history is set up by the Stack Exchange team throughout the network, but it provides a functionality that I find very similar to the one offered by the approach I suggest. It would be helpful to examine closely this Stack Exchange process so that you can get a broader perspective about the ways in which scenarios of this nature can be managed.

---

Endnote

_{¹ Integration Definition for Information Modeling (IDEF1X) is a highly recommendable data modeling technique that was established as a standard in December 1993 by the U.S. National Institute of Standards and Technology (NIST). It is solidly based on (a) the early theoretical material authored by the sole originator of the relational model of data, i.e., Dr. E. F. Codd; on (b) the entity-relationship view, developed by Dr. P. P. Chen; and also on (c) the Logical Database Design Technique, created by Robert G. Brown.}