Temporal Databases: Es posible tener tablas temporales en nuestras aplicaciones?

Casi todas las aplicaciones necesitan algún tipo de manejo temporal de sus operaciones.

Supongamos que tenemos la tabla de clientes y la modelamos de la siguiente forma:

*ClienteId
ClienteDireccion

ClienteID	ClienteDireccion
MBI	18 de Julio 3234

Que pasa cuando los datos de una entidad cambia?.
Lo que se hace habitualmente es cambiar directamente el valor de la tabla, cambiando de ahí hacia adelante todos las referencias a la misma.
Si el cliente MBI, cambia su dirección, el registro que lo representa quedará así:

ClienteID	ClienteDireccion
MBI	Millán 2515

Supongamos ahora, que el cliente realiza compras antes del cambio de domicilio y las consultamos después del registro del cambio de domicilio.

Lo que pasaría es que los nuevos reportes van a figurar con la dirección nueva, pudiendo ocasionar problemas de entrega.

Cual seria una posible solución?
Una solución sencilla, seria manejar vigencias en los datos del cliente, donde podamos especificar un intervalo en los cuales la información del cliente es valida.

*ClienteID
*ClienteVigenciaFechaInicial
ClienteVigenciaFechaFinal
ClienteDireccion

Con este nuevo modelo, la tabla de clientes quedaria

ClienteID	ClienteVigenciaFechaInicial	ClienteVigenciaFechaFinal	ClienteDireccion
MBI	01-01-2018	25-02-2018	Plaza Independencia 812
MBI	26-02-2018	31-12-9999	Millán 2515

Ahora debemos adaptar los programas para que en vez de ir a buscar únicamente por la clave de la tabla (Id de cliente) busque con la clave del cliente y una fecha que coincida en el rango de vigencia.

Podríamos definir una vista que reciba una fecha y devuelva los datos del cliente en dicha fecha.

Este tipo de modelado, trae unos cuantos problemas nuevos, pues hay que asegurarse que los rangos de vigencia sean validos, no se superpongan, asegurarse de no tener huecos en la vigencia (en caso que la aplicación así lo exija), etc.

Como se puede ver, la tabla solo necesita agregar registros, pues nunca vamos a hacer una actualización sobre la misma. Cuando quiero borrar un dato, lo único que tengo que hacer es cerrar la vigencia a partir de la fecha que quiero que no se utilice mas.

Ahora surge un problema nuevo. ¿Que tengo que hacer, si tengo que corregir la vigencia que ingrese por error?. Quienes han estudiado mas le tema, lo resuelven con otro rango de fechas, donde se dice cual donde es valido el rango de vigencia.  Con dos rangos de fechas (bi-temporal database) se puede recomponer el estado de la tabla en cualquier momento.

Este problema es tan común que fue estandarizado hace varios años en el SQL:2011 y de a poco las diferentes bases de datos han ido incorporando en sus productos.

Copiado de Wikipedia

• MariaDB version 10.3.4 added support for SQL:2011 standard as "System-Versioned Tables".
• Oracle Database – Oracle Workspace Manager is a feature of Oracle Database which enables application developers and DBAs to manage current, proposed and historical versions of data in the same database.
• PostgreSQL versión 9.2 added native ranged data types that are capable of implementing all of the features of the pgFoundry temporal contributed extension. The PostgreSQL range types are supported by numerous native operators and functions..
• IBM DB2 version 10 added a feature called "time travel query" which is based on the temporal capabilities of the SQL:2011 standard.
• Microsoft SQL Server introduced Temporal Tables as a feature for SQL Server 2016. 

En todas estas base de datos, se puede indicar que una tabla será versionada y generalmente se le especifica cual es el rango de fecha de vigencia y a partir de ese momento se mantiene automáticamente la historia de todos los cambios. 

Lo que es mejor, se tiene también una vista que puede consultarse y recuperar el estado de la entidad en cualquier momento de una fecha pasada (y futura). 

Para esto, se agrega a las sentencias sql de consulta la palabra clave FOR SYSTEM_TIME. Por ejemplo, en SQL Server, para consultar una tabla en una fecha especifica se pone: 

SELECT *   FROM [dbo].CLIENTES   FOR SYSTEM_TIME AS OF '2018-01-25' WHERE CLIENTEID='MBI'

También se puede consultar, por rangos de fecha, por todas las versiones de una tabla, etc.

Pongo un ejemplo de una aplicación aduanera. El año pasado se hizo una importación y se liquidaron los impuestos con los acuerdos vigentes a esa fecha. Ahora supongamos que en estudios a posteriori se detecta que la importación era de un producto diferente que pagaba mas impuestos.  El sistema debe re-liquidar los impuestos con todos los valores de la fecha de importación. Durante 5 años esto debe mantenerse y en 5 años, hay países que cambian de nombre, acuerdos comerciales que cambian, productos que dejan de existir, empresas que ya no existen, etc. Todos esas entidades deben mantenerse y poder recuperarse tal cual estaban en el momento de la importación.
Si se pudieran marcar todas las tablas involucradas en una liquidación como temporales, y la aplicación permitiera especificarle una "fecha de liquidación" en el pasado que viera los datos a esa fecha, la aplicación seria MUCHISIMO mas sencilla.

Parece que hemos llegado a una madurez suficiente como para que las herramientas de desarrollo (entre ellas GeneXus) puedan soportar tablas temporales y dar así mucha mas potencia a nuestras aplicaciones, escondiendo una complejidad a los desarrolladores. 

Lei un par de libros sobre el tema :
Managing Time in Relational Databases: How to Design, Update and Query Temporal Data fue el que mas me gustó, porque lo entendí :). 

Temporal Data & the Relational Model (Date) Me resultó bastante pesado y no pude terminarlo, pues es bastante teórico.

Me da la sensación que el proveedor que pueda resolver este problema en forma sencilla para los desarrolladores va a tener una ventaja importante en el mercado.

UPDATE: Ante la consulta de un colega, agrego que al tener este tipo de tablas temporales, me ahorro tambien muchos registros de auditoria (quien cambio que y cuando lo hizo) porque si agrego los campos de usuarios en las tablas, quedan todos los cambios registrados en la misma.

Una implementacion posible de este soporte en el DBMS es la que tiene SQL Server que genera una tabla con todos los datos historicos (CLIENTES_HISTORY) y ademas una vista con el valor corriente (CLIENTES).  De esa forma, los programas que usan la tabla de clientes siguen funcionando correctamente y los programas que usan la nueva sintaxis de consultas con manejo temporal, pueden usar las tablas de datos historicos, todo eso de forma bastante transparente para el desarrollador.