GuilleSQL :: Microsoft SQL Server, SSIS, y más !!

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

Volver a: [Trabajar con tablas versionadas (Fecha Desde y Fecha Hasta) en SQL Server, Slowly Changing Dimension (SCD) Tipo 2]


Este Capítulo examina las diferentes alternativas posibles cara a la necesidad de definir de Claves Primarias o Índices Únicos sobre tablas versionadas (con Fecha Desde y Fecha Hasta) debido al potencial riesgo de duplicidad de valores (violación de claves) si utilizamos sólo la Clave de Negocio (Business Key). Las Claves Primarias o Indices Unicos permiten garantizar la integridad de los datos en lo referente a la unicidad de filas, facilitan tareas como comprobar versiones solapadas, etc. En entornos de Business Intelligence (BI) y Data Warehouse (DW) facilitan la creación de Claves Subrogadas en tablas de Dimensiones de Cubos OLAP, etc. ¿Puede interesarnos un valor autonumérico o secuencial? ¿Puede interesarnos un campo Fecha Hora de Creación o Timestamp?

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.

Volver a: [Trabajar con tablas versionadas (Fecha Desde y Fecha Hasta) en SQL Server, Slowly Changing Dimension (SCD) Tipo 2]




Miembros de
Miembros de GITCA (Global IT Community Association)

Menu de Usuario
  Iniciar Sesión
  Registrarse
  Restablecer Contraseña
  Ventajas de Registrarse

Acerca de
  Contigo desde Oct 2007
  771 usuarios registrados
  86146 pageloads/mes
  Ranking Alexa 498160

Social Networks
Sigue a Portal GuilleSQL en Linkedin !!
Sigue a Portal GuilleSQL en Twitter !!



Archivo

Junio de 2016 (1)
Mayo de 2016 (2)
Abril de 2016 (2)
Septiembre de 2015 (2)
Agosto de 2015 (2)
Junio de 2015 (10)
Mayo de 2015 (4)
Abril de 2015 (8)
Marzo de 2015 (11)
Octubre de 2014 (3)
Septiembre de 2014 (7)
Agosto de 2014 (5)
Julio de 2014 (2)
Mayo de 2014 (4)
Abril de 2014 (4)
Marzo de 2014 (4)
Febrero de 2014 (1)
Enero de 2014 (5)
Diciembre de 2013 (8)
Noviembre de 2013 (2)
Octubre de 2013 (7)
Septiembre de 2013 (6)
Agosto de 2013 (1)
Julio de 2013 (6)
Junio de 2013 (11)
Mayo de 2013 (7)
Abril de 2013 (6)
Febrero de 2013 (5)
Enero de 2013 (7)
Diciembre de 2012 (12)
Noviembre de 2012 (13)
Octubre de 2012 (5)
Septiembre de 2012 (3)
Agosto de 2012 (6)
Julio de 2012 (4)
Junio de 2012 (1)
Mayo de 2012 (2)
Abril de 2012 (7)
Marzo de 2012 (16)
Febrero de 2012 (9)
Enero de 2012 (5)
Diciembre de 2011 (10)
Noviembre de 2011 (10)
Octubre de 2011 (4)
Septiembre de 2011 (5)
Agosto de 2011 (2)
Julio de 2011 (2)
Junio de 2011 (4)
Mayo de 2011 (2)
Abril de 2011 (6)
Marzo de 2011 (4)
Febrero de 2011 (10)
Enero de 2011 (5)
Diciembre de 2010 (6)
Noviembre de 2010 (4)
Octubre de 2010 (8)
Septiembre de 2010 (4)
Agosto de 2010 (1)
Julio de 2010 (3)
Mayo de 2010 (5)
Abril de 2010 (6)
Marzo de 2010 (8)
Febrero de 2010 (3)
Enero de 2010 (1)
Diciembre de 2009 (9)
Noviembre de 2009 (14)
Octubre de 2009 (2)
Septiembre de 2009 (8)
Agosto de 2009 (2)
Julio de 2009 (10)
Junio de 2009 (9)
Mayo de 2009 (10)
Abril de 2009 (9)
Marzo de 2009 (3)
Febrero de 2009 (2)
Enero de 2009 (3)
Noviembre de 2008 (2)
Octubre de 2008 (2)
Septiembre de 2008 (2)
Agosto de 2008 (5)
Julio de 2008 (5)
Junio de 2008 (1)
Mayo de 2008 (3)
Abril de 2008 (2)
Marzo de 2008 (2)
Febrero de 2008 (2)
Enero de 2008 (5)
Noviembre de 2007 (2)
Octubre de 2007 (2)






Copyright © 2007 GuilleSQL, todos los derechos reservados.