In the first post of the series, I gave an overview of how to represent attributes on a record that change over time. In the second post, I showed how to combine stable and variable attributes when looking up a record.
Storing variable attributes in a versioned table with a date range lookup provides the infrastructure to look up which version of an attribute is applicable for a given date. In order to always be able to query out which versioned record is effective for a given date, we must maintain invariants such as no gaps or overlaps in the date range and the last versioned record being effective until “infinity.”
Date ranges for the versioned records should not overlap, as we only ever want one versioned attribute to be effective for a specific date. Today I’ll dive into how to maintain the “no overlaps” invariant.
Remember the Loan Example?
Last post, I used a loan table as an example of a versioned record. The variable aspects of the loan like the rate live on the versioned table. The stable attributes of the loan like the name and principal live on the base table.
Please, No Conflicting Loan Versions
We wouldn’t want the loan versioned records to have overlapping ranges because then there could be more than one versioned record effective for a given date.
In the above example, we wouldn’t know if the interest rate effective for, say, 07/15/2019 was meant to be 3% or 4%.
Preventing Conflicting Versioned Records
We know we don’t want conflicting loan versions for an effective date. But how would we enforce the constraint that versioned records do no overlap, preferably at the database level? We can use a PostgreSQL exclusion constraint to guarantee non-overlapping effective date ranges for any given loan.
An exclusion constraint is similar to a unique constraint in that it checks that each row has a different value for a column or a set of columns. An exclusion constraint, however, can check columns for more than equality. It ensures that if any two rows are compared on the specified columns using the specified operators, at least one of the operator comparisons will return false.
Per PostgrSQL docs, the basic syntax on an exclusion constraint looks like this:
EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, ... ] ) .
We never want the effectiveDateTimeRange column on the LoanVersion table to have overlapping values for versioned records belonging to the same loan (i.e. versioned records with the same headerId). We’ll create an exclusion constraint like this:
ALTER TABLE "LoanVersion"
ADD CONSTRAINT no_overlapping_date_time
exclude USING gist (
“headerId” WITH =,
"effectiveDateTimeRange" WITH &&);
The specified columns are headerId and effectiveDateTimeRange. The headerId column is tested for equality with the = operator. The effectiveDateTimeRange column is tested for overlap with the && operator.
The exclusion constraint ensures that when any two rows are compared, either the headerId comparison or the effectiveDateTimeRange comparison will return false. If two rows have the same headerId, they must not have overlapping ranges (or if two rows have overlapping ranges, they must not have the same headerId). In other words, rows associated with the same loan (having the same headerId) cannot have overlapping effective date time ranges.
With this exclusion constraint in place, we are now using the database to enforce that versioned records belonging to the same base record do not have overlapping effective date ranges.
This post is part of a series on versioned records:
- Storing Variable Attributes in a Version Table
- Looking up a Record with Variable Attributes
- Preventing Overlapping Ranges with Database-Level Exclusion Constraints