I suggest that we add temporal foreign keys to relational database design.

Programs move data.  Databases store data in a consistent fashion.  These different purposes can lead to different organizing principles.  One of the key reasons for Object Oriented structures is to minimize cost and complexity when things change over time.  This creates a temporal relationship between various designs, a relationship that is powerfully supported by object oriented development. 

However, while programs hold functions that change over time, we don’t have a good structure for isolating complexity caused by data that changes over time.  And so I ask the question: How do we begin to create the notion, in the data storage layer, that data can hold a temporal relationship with other data. 

I don’t mean the notion that a data record would have a “last updated date”.  I mean the notion that a data table may contain a foreign key to another table, where a record keeps the values in the related table that existed on a particular date, even if the data in the related table is later changed. 

For example: let’s say that company A sells products.  Their products are P1 and P2, and they sell for $20 each.  Now, company B makes an order for product P1.  In current RDBMS systems, we actively copy the price at the time of the order from the ‘products’ table to the ‘purchase order details’ table because the price could change later, and we want to remember the price on the date it was made.

However, this is a workaround.  The fact is that the purchase order has a temporal relationship with the products table… a relationship that the notion of RDBMS cannot handle… so we copy fields around.  The decision of what fields to copy belongs to the ‘purchase order details’ table, and the code becomes complex by the notion that specific fields have to be selected from that table instead of the products table when evaluating a product.  It’s a kind of “overlay”.  The relationship says: pick fields from the related table unless a field by that name happens to exist in the current table. 

Should this relationship be defined by the ‘order details’ table?  Shouldn’t the owner of the data (the products table) decide what columns to expose as “time related” while other columns are not?  I submit that this would put responsibilities where they belong and reduce the complexity of the data systems themselves.

I suggest this innovative feature for RDBMS systems: a temporal foreign key.  The owner of a table indicates what fields are likely to change frequently and would have their data kept in a temporal structure, while other fields are not temporal (like a relationship with the bill of materials used for new shipments).  Then, when a foreign key is made, by placing the product id into the ‘order details’ table, the date of the relationship is noted.  Temporal field values are fixed at the value in place at that time.  No reason to copy fields to another table. 

The code in the calling systems would be much simpler, as would the designs of the databases themselves.  The complexity of the database system would increase, but not moreso than other forms of referential integrity. 

It is time to consider this kind of relationship as an innovation to the now 30 year old basic notions of relational databases.