Esta pregunta está relacionada con el esquema que se puede encontrar en una de mis otras preguntasaquí. Básicamente en mi base de datos almaceno usuarios, ubicaciones, sensores, entre otras cosas. Todos estos elementos son editables en el sistema por los usuarios y borrables.

Sin embargo, cuando se edita o elimina un elemento, necesito almacenar los datos antiguos; Necesito poder ver cuáles eran los datos antes del cambio.

También hay elementos no editables en la base de datos, como "lecturas". Son más de un registro realmente. Las lecturas se registran en los sensores, porque es la lectura de un sensor en particular.

Si genero un informe de lecturas, necesito poder ver cuáles eran los atributos para una ubicación o sensoren el momento de la lectura.

Básicamente, debería ser capaz de reconstruir los datos para cualquier momento.

Ahora, he hecho esto antes y lo hice funcionar bien agregando las siguientes columnas a cada tabla editable:

valid_from
valid_to
edited_by

Si valid_to = 9999-12-31 23:59:59 entonces ese es el registro actual. Si valid_to es igual a valid_from, se elimina el registro.

Sin embargo, nunca estuve contento con los desencadenantes que necesitaba usar para aplicar la consistencia de la clave externa.

Posiblemente puedo evitar los desencadenantes utilizando la extensión de la base de datos "PostgreSQL". Esto proporciona un tipo de columna llamada "período" que le permite almacenar un período de tiempo entre dos fechas, y luego le permite hacer restricciones de COMPROBACIÓN para evitar la superposición de períodos. Esa podría ser una respuesta.

Sin embargo, me pregunto si hay otra manera.

He visto a personas mencionar el uso de tablas históricas especiales, pero realmente no me gusta la idea de mantener 2 tablas para casi cada 1 tabla (aunque aún podría ser una posibilidad).

Tal vez podría reducir mi implementación inicial para no molestarme en verificar la consistencia de los registros que no son "actuales", es decir, solo molestarme en verificar las restricciones en los registros donde valid_to es 9999-12-31 23:59:59. Después de todo, las personas que usan tablas históricas no parecen tener verificaciones de restricciones en esas tablas (por la misma razón, necesitaría desencadenantes).

¿Alguien tiene alguna idea sobre esto?

PD: el título también menciona una base de datos auditable. En el sistema anterior que mencioné, siempre está el campo edited_by. Esto permitió rastrear todos los cambios para que siempre pudiéramos ver quién cambió un registro. No estoy seguro de cuánta diferencia podría hacer eso.

Gracias.