How would you make a Temporal Many-to-Many Relationship in SQL?

Question

How would you represent a temporal many-to-many relation in SQL? Under non-temporal circumstances one would use a junction table (aka link/bridge/map) to connect the two sides.

Is adding temporal tracking as simple as including a ValidStart and ValidEnd columns on the junction table? If you have done this, what issues (if any) did you run into? Is there a better method for keeping track of changes over time in this kind of relation?

If it helps at all, in my case I'm specifically using SQL 2008 and the temporal data is not bitemporal as I'm only tracking valid time.

Answer 1

I am working on a project (for some years now) that uses both temporal data and temporal many-to-many relations. Each table has ValidFrom and ValidUntil columns (storing dates only).

First you have to define the semantics of the Valid* columns, i.e. whether ValidUntil is included or excluded from the validity range. You also need to specify whether NULL dates are valid and what their meaning is.

Next you need a couple of functions, such as dbo.Overlaps2() and dbo.Overlaps3() which receive 2 and 3 date ranges respectively, and return 1 if the date ranges overlap and 0 otherwise.

On top of that, I defined views for the many-to-many relationships with dbo.Overlap3(...)=1.

One further point is to have a set of functions which calculate the effective validity range based on dates in 2 or 3 related tables.

Recently I had to add functionality to allow a user to display all available data, or only currently valid data. I save this setting in a users table, associate the SPID to the user when opening a connection, and filter the records in another set of views.