Fabulous IT

One of my better (or worse in some people's opinion) properties as a programmer is my fairly idealistic view. I've become more familiar with the relational model over the past few years and am very attracted to the purist approach: high normalization. I also feel strongly about avoiding the abuse of Null values, especially where relational columns are concerned. These are of course ideals, and not always practical given time constraints and complexity. I do however feel that there is a special niche for relational database technology. It can't really be treated just like a trivial storage medium, and it also isn't a simple extension of programming activities. I feel that the best database approach is one that is clean (normalized) and simple in terms of architecture (harnesses patterns, but may have many many tables). I also feel that abstraction of database specifics from procedural code is valuable. This ensures that solutions are properly relational in the database, but properly modeled from a procedural point of view.

A book I read last year: Temporal Data and the Relational Model (Date, 2003) was very through, but didn't really solve any of the inherent issue in modeling data over time. It proposed solutions, but nothing profound. The primarily useful concepts this book covered were in issues of ambiguity.

Journey is the code name for an application I am building for work. The application doesn't have a real product name yet. Issues of time are central to a lot of the things managed in this application. I have struggled a lot to model time appropriately and I think I have finally settled on a model that will fit the needs.

First and foremost, Journey aims to be database technology agnostic. It does have a certain set of minimum relational feature requirements, but the goal is to avoid anything on the database end that is non-standard and not widely supported. In the long term, Journey is agnostic to the format time is stored in. There are some inherent dependencies in the early code base between Journey and a MySQL implementation, but these will be abstracted. Journey supports both date (Year, Month, Day) and date-time (date + Hour, Minute, Second) formats.

In the case of each, the entire unit of time is considered a block of time starting with the instant that block starts and ending just before the next sequential unit beings (start inclusive, tail exclusive). This avoids one of the primary concerns with ambiguity in time values. Sequential time ranges should not overlap.

Records in a Journey database table that have only one time field are considered instantaneous over the range of time (a day or a second) the column represents. For columns that represent the start of something, the start is the very beginning of that range. For columns that represent the end of something, the end is at the very last instant of that range. Columns of this type may also indicate something happening any non-specific instant between the start and end of the range. If a table tracks when files were deleted for example, and only tracks dates (not full time in seconds) then the instant is sometime on that date, but not necessarily the first or last instant. This is subtly different from the use case where a table tracks when files will be deleted. For this reason, all time columns in journey are considered either informative, normative starting, or normative ending. Informative means "any time in this range", normative starting means "at the first instant of this range", and normative ending means "at the last instant of this range". If a prescriptive delete date is normative starting on a date, it should happen at the very beginning of that day. If a prescriptive delete date is normative ending on a date, it should happen at the very end of that day.

In addition to tables that contain a single time column, tables may contain a normative starting and normative ending pair. Tables in general may have any number of informative, normative starting and normative ending time columns.  However, the only allowed functional pair is a normative starting and normative ending. For modeling purposes, Journey considers this pair to be a single column, though in many relational implementations they may be two. It matters little (in theory) if the underlying storage is an actual time value pair, or a start time and a number of units the range spans.

At any given time, a value in a time column will have one of four variable values in relation to any other time value: Null, Past, Current, Future. As I have stated before, I dislike abusing Null values. To be clear, Null is a valid non-value. It is however suggested by some idealists that Null values should not be stored in a Database, and will go to great lengths to prove why it is unnecessary to do so. I consider myself to be in a class of people that agree with this, but have to work with people that do not see the big deal.

Journey does not inherently allow the storing of Null. In so far as it is concerned with the installation of the databases it uses, Journey specifies when possible that no column may be Null provided the database technology supports a reasonable method for default values. Interactions between specific business logic requirements and database technology limitations may necessitate the use of Null in the absence of no reasonable default value, but this is considered an intermittent last resort.  I reject the notion that default values should be allowed to violate column constraints.

That having been said, it is perfectly valid for operations to return a Null value. The operation of comparing two dates returns Null in the event either of the two operands is Null or in some way invalid (assuming the operation does not throw an exception). In MySQL, the use of 0 dates and 0 times (0000-00-00) is an example of an invalid date that is valid in column constraints. All time operations involving  a 0000-00-00 will return Null.

Journey primarily cares about three qualitative property values when comparing two time values: is the first before (past), during (current), or after (future) the second. There is an interesting consequence to the way Journey handles time ranges. My preference was to have Journey store only the starting time unit and an integer number or value one or greater to indicate the number of units the range encompasses. This turned out to be imprudent with the MySQL implementation and the more database technology agnostic features have been side-tabled in the interest of progress until more development time is available. In an ideal world, it would be irrelevant how the database stores the data. In the meanwhile, it is stored as two time values.

The only issue with storing two time ranges is the lack of constraint of one column's value on another. The second date in the range should never come before the first. The "invalid" values outlined below are a result of such possibility and are not really part of any value that would be stored. This possibility is listed for completeness only.

Comparing a time range to the current time results in a few more interesting quality values than the four mentioned above between two. These possibilities for the basis for queries involving time ranges in Journey. The chart on the right below illustrates the logical reduction of these return values, and their explanation is on the left. The possibilities are not all mutually exclusive. So a method that returns all relevant values would be multiplexed. Alternatively, a set of operators could return true or false for each possible quality.

When start and end time are Null or simply each individually invalid, the result is "Unknown" (u). This means that the record has no known associated start or end date.

If there is an end time, and it is in the Past, the result is "Past". This means that the start time is implicitly in the past, even when invalid. If the start time is explicitly Current or Future then the result would be "Invalid", though this value should not be allowed to store.

If there is any non-null end time, and a null start time the result is "Past Indefinite". This means that the start time is unknown, but the end time is known. Similarly if there is an non-null start time and a null end time the result is "Future Indefinite" which indicates there is a known start time, but no known end time.

If there is a current or future end time (and a start time not after the end time which would be "Invalid"), then the result is "Past Pending". This means that there is a known end time, but it has not happened yet. The reverse of this would be "Future Pending", but as it turns out there is no valid set of inputs where "Future Pending" is different than "Future" and invalid inputs are irrelevant.

If either the start time or end time are current, or the start time is null or past and the end time is future (again assuming the start time is not after the end time), then the result is "Current". This indicates the current time is within the time range.

Since "Future Pending" and "Future" are logically the same, the later is folded into the other. If the start time is future and the time range is not invalid, the result is "Future".

An interesting problem is that it is unclear how to model events that are temporally ubiquitous, since defining no start time and no end time is ambiguous. It could mean all times, or no time. If the former is the case, then "Unknown" would be synonymous with "Current". How this case will be modeled in Journey is still under consideration. Ideally, records that have an integrated time range by virtue of table definition including out-going foreign relations would only exist if some portion of their temporal nature is known, so either start or end time if not both should be required valid (i.e. no 0000-00-00). For records that receive a foreign relation from another table to attribute temporal data, the presence of a related time range with the Unknown property would indicate all times while the absence of any related time range would indicate no times. The problem with this is it means Null and Unknown would be distinct, and that Unknown should only result when one (if not both) of the operands is invalid, but not Null.