Cómo definir una Clave Unica o Indice Unico sobre una tabla con Versionado (Fecha Desde y Fecha Hasta)

Una tarea muy habitual al trabajar con Bases de Datos, es la definición de una Clave Primaria o de Índices Únicos, es decir, identificar un campo (o un conjunto de campos, es decir, una clave compuesta o índice compuesto) que identifica de forma unequívoca cada fila de nuestra tabla de base de datos. Cuando no trabajamos con tablas versionadas, suele utilizarse la denominada Clave de Negocio (Business Key ó BK), u otros campos que ofrezcan unicidad. Por ejemplo, si tenemos una tabla de Clientes, la Clave de Negocio (Business Key) será el Código de Cliente, aunque quizás también podríamos crear un índice único sobre el campo correo electrónico del Cliente. Al trabajar con Tablas Versionadas (Slowly Changing Dimensions - SCD - de tipo 2), pueden utilizarse Claves Compuestas, Campos Autonuméricos (Claves Subrogadas), etc.

Al trabajar con tablas versionadas (con Fecha Desde y Fecha Hasta), esta tarea se complica ligeramente, ya que la Clave de Negocio (Business Key ó BK) no es suficiente (existencia de duplicados), como resultado de la posible existencia de múltiples filas (es decir, múltiples versiones) para la misma Clave de Negocio. Por poner un ejemplo, podemos tener dos versiones para el mismo Cliente, quizás porque el Cliente cambió su Denominación Social generándose como consecuencia la existencia de dos versiones: la primera con la Denominación Social original, y la nueva con la Denominación Social actual (eso sí, cada una con los campos Fecha Desde y Fecha Hasta correctamente rellenos conforme a la fecha del cambio).

Como consecuencia de todo esto, la definición de una Clave Primaria o de un Índice Único sobre una tabla versionada (con Fecha Desde y Fecha Hasta) suele ser una duda común. ¿Qué alternativas tenemos? Las posibilidades que tenemos son las siguientes:

• No crear ni Clave Primaria ni Índice Único. En consecuencia, sólo crearemos índices no únicos para satisfacer las consultas SQL, y conseguir un buen rendimiento de la base de datos, pero sin tener posibilidad de identificar cada fila de forma unequívoca.
• Crear una Clave Única compuesta por la Clave de Negocio (Business Key) y la Fecha Desde (o la Clave de Negocio y la Fecha Hasta, como queramos ;-). Esto sólo lo podremos hacer si nuestro sistema de versionado no permite versiones con Fecha Desde = Fecha Hasta, por lo que si seguimos el Modelo de Versionado propuesto en éste artículo, deberemos descartar esta opción (nos generaría violaciones de clave/índice, es decir, duplicados).
• Crear un campo Autonumérico o Secuencial (que almacene números secuenciales, aunque no se trate de un entero con IDENTITY), y seguidamente, crear la Clave Única sobre el nuevo campo creado. Esto coincide con lo que se conoce como Clave Subrogada.
  En cualquier caso, bajo mi opinión (otros piensan radicalmente lo contrario) es recomendable evitar la utilización de campos autonuméricos debido a que si fuera necesario volver a regenerar la tabla (total o parcialmente), si la asignación de los valores autonuméricos no se realizase exactamente de la misma forma y existiesen claves externas de otras tablas apuntando a dicho campo autonumérico (ej: caso habitual entre tablas de hechos y tablas de dimensiones en entornos OLAP y Business Intelligence) nos encontraríamos ante un serio problema de integridad.
• Crear una Secuencia para cada Clave de Negocio (Business Key ó BK), es decir, un campo que para cada versión de una misma entidad (ej: de un mismo Cliente, si fuera el caso de una tabla Clientes con versionado) almacene números secuenciales (ej: 1, 2, 3, etc.) en cada una de las versiones, permitiendo tanto direccionar de forma unequívoca una fila cualquiera de la tabla, como seleccionar todas las versiones de una entidad (ej: de un cliente, siguiendo con el ejemplo de antes) ordenadas correctamente (incluso si existen múltiples versiones con Fecha Desde = Fecha Hasta como causa de múltiples cambios en el mismo día).
• Crear un campo con la Fecha Hora de Creación, y seguidamente crear una Clave Única compuesta por la Clave de Negocio y la Fecha Hora de Creación de la versión. Recordar que el tipo Timestamp de SQL Server no representa una Fecha Hora, sino sencillamente un número único de 8 Bytes en la base de datos asociado al contador de marca de hora de una base de datos. Además, la recomendación de Microsoft es no utilizar campos Timestamp en claves. Se debe tener en cuenta, que en otros motores de base de datos, el comportamiento de sus tipos Timestamp puede ser diferente al comportamiento existente en SQL Server. En cualquier caso, aunque esta posibilidad puede parecer muy apetecible, deberemos descartar esta alternativa, ya que dos transacciones consecutivas podrían insertar dos filas en la misma tabla con la misma Fecha Hora de Creación (violación de clave al canto...). Al menos es así hasta SQL Server 2005, utilizando el tipo de dato DATETIME, el cual tan sólo tiene una precisión de 3,33 milisegundos (hablo por experiencia propia, es decir, aunque en muchos casos no ocurre, no me resultó muy dificil encontrarme con dos versiones con misma Fecha Hora de Creación).
• Crear un campo Fecha Hora de Creación (Timestamp), y utilizarlo como Clave Primaria o Índice Único. Pues sí, existen seres humanos que así diseñan sus tablas. Es cierto que este método cumple con la unicidad, pero coño, esto es menos elegante que vestirse con las bragas por fuera !! Especialmente en SQL Server, ya que la utilización del tipo Timestamp no es muy recomendable (recordar que en SQL Server un Timestamp en un valor numérico propietario, no un valor de tipo Fecha Hora como ocurre en otros motores como DB2).

A vista de lo anterior, quizás la alternativa más interesante sea apoyarse en un campo que almacene la Secuencia de cambio (es decir, un contador de versiones), pudiendo crear un índice compuesto por la Clave de Negocio (Business Key ó BK) y por dicho campo de Secuencia de cambio. De este modo, conseguimos varios objetivos:

• Conseguir nuestra ansiada unicidad, a través de la anterior composición de campos, esto es, Clave de Negocio (Business Key ó BK) y Secuencia de cambio.
• Conocer el momento de creación de la fila en nuestra tabla, en el caso de incluir un campo Fecha Hora de Creación, ya que los campos Fecha Desde y Fecha Hasta (al igual que Fecha Alta y Fecha Baja), pueden no ser representativos del momento de creación de la fila en la base de datos (ej: para conseguir un efecto retroactivo, para realizar una corrección excepcional en la base de datos, etc.).
• Poder obtener las distintas versiones ordenadas correctamente, de tal modo, que si tenemos varias versiones con Fecha Desde = Fecha Hasta para el mismo día, podamos conocer correctamente la secuencia de cambios que se realizarón, correctamente ordenados. Este detalle puede resultar en algunas ocasiones de utilidad.

En entornos de Data Warehouse (DW) y Business Intelligence (BI), muchos prefieren la utilización de campos autonuméricos para resolver esta situación. Personalmente, bajo mi opinión esta será la mejor solución en muchos casos, pero en otros muchos no será así (insisto, bajo mi opinión), por lo que mi recomendación es examinar cada caso por separado y jamás tomar esta regla como una regla general.

En principio, aquí acaba el problema de la unicidad en las tablas con versionado de datos (ojo, que se podría complicar mucho más, pero bajo mi criterio, creo que es suficiente). Con esto, otro problema identificado y resuelto, así que, adelante con el siguiente capítulo.