Diseño de database de entidades de múltiples versiones

Actualmente estoy trabajando en un proyecto en el que tenemos que mantener múltiples versiones de una entidad determinada en la database.

Por ejemplo, supongamos que tenemos una entidad llamada House que tiene habitaciones. Ahora, cada vez que se agrega una habitación, se actualiza o se elimina de la casa, o se cambia una propiedad de la casa o cualquiera de las habitaciones, debemos conservar tanto la información anterior como la nueva. Preferiblemente, nos gustaría agrupar los cambios en un "set de cambios" (realmente el problema es como el control de fuente).

Ahora el model real es bastante más complicado que esto, pero creo que este ejemplo ilustra el problema.

Me gustaría mucho escuchar algunas posibles soluciones a este problema.

Estamos ejecutando Microsoft SQL Server 2008 R2.

En los almacenes de datos, esto generalmente se resuelve usando una Dimensión que Cambia Lentamente (SCD) .

Como un breve resumen, puede:

  • Cree una key sustituta (una identificación de instancia) y use la key natural y un número de versión (o range efectivo de date / hora) para realizar un seguimiento de los cambios (Tipo 2 SCD)
  • Tenga N columnas en su tabla de sala para N versiones (Tipo 3 SCD)
  • Tener una tabla separada "rooms_history" y solo mantener la instancia actual en la tabla de habitaciones (Tipo 4)

Mi equipo ha usado tradicionalmente una combinación de Tipo 2 (para datos más "recientes") y Tipo 4 (para datos "anteriores" en un file). change_id cambios utilizando un change_id en los objects que se cambian, que es una reference a un object de change que incluye la date del cambio, así como otros metadatos (usuario, comentarios, etc.)