Retaining the particulars of change over time is a fairly intricate configuration. Audit log or shadow tables are sometimes employed, but on occasion there is a need for the "old" and "new" rows to exist in a single operation table for application use. Far too often, the implementation of temporal data structures is shoddy, loose, and imprecise; rather than the fairly complex dance move such temporal arrangements must perform in actuality. The sub-optimal result is much like one's performance of the Funky Chicken at a friend's wedding; the desired moves are mimicked, after a fashion, but it is unlikely to earn high marks on "So You Think You Can Dance." The usual temporal implementation simply slaps on start and stop dates, debates a little over default date values versus NULLs, then moves on to the next subject.
Often the development team is blinded because the necessity for representing time is regarded as simple, easy, typical, and expected. Consequently, it is considered a waste of time to spend time on time. While the fictional character Dr. Who can make time changes look like a simple flip of the switch, in this case, there is more than switch-flipping going on. When time enters the picture, new requirements emerge, such as the expansion of the object key to include the start date/time for uniqueness. Across the rows containing the same non-temporal object key value, start and stop date ranges must not overlap; again, within a non-temporal object key value only one row can be "current" (having NULL or the "future" default date value within the stop date column). The DBMS only nominally assists in this situation. Start dates can be defined to exclude NULLs, even default the column to the current date/time value. The object key plus a start date can become a candidate key in the design and be used as a primary or alternate key. But those constraints are about the limit of the DBMS assistance, relationally.
More conditions must be enforced.
- Within a row, the start date must be less than the stop date (assuming the stop date is not NULL).
- If the stop date is NULL (or the default future value), then the row's start date must be greater than all other all stop dates across the rows within the same non-temporal object key.
- If the stop date is populated, it must be less than the start date of any row having the same non-temporal key value with a start date greater than its own start date value.
- The start date value for any row "X" must be greater than the stop date of the row having the same non-temporal object key value and the maximum start date value less than the X's start date value.
The enforcement of these rules could be accomplished using constraints and triggers, or even CLR, but that does not make it DBMS-supported. It is all still custom logic to be coded and tested long after the DBMS is installed.
It would be really nice if DBMS vendors incorporated the application of some of these temporal rules directly within the DBMS. Having to support changes related to time for an object is indeed typical and expected. The presumption that rules like those mentioned above could be optimized within the DBMS itself does not seem too wild. Just imagine how much easier system development would be if a "support-changes-over-time" flag and a non-temporal key identifier were but parameters used in defining a table that implemented table row level start and stop dates and tested the non-overlapping rules on each insertion or update. One could always turn off the parameter if one really and truly wanted to code that logic inside the application.