Historical / auditable database
- by Mark
Hi all,
This question is related to the schema that can be found in one of my other questions here. Basically in my database I store users, locations, sensors amongst other things. All of these things are editable in the system by users, and deletable.
However - when an item is edited or deleted I need to store the old data; I need to be able to see what the data was before the change.
There are also non-editable items in the database, such as "readings". They are more of a log really. Readings are logged against sensors, because its the reading for a particular sensor.
If I generate a report of readings, I need to be able to see what the attributes for a location or sensor was at the time of the reading.
Basically I should be able to reconstruct the data for any point in time.
Now, I've done this before and got it working well by adding the following columns to each editable table:
valid_from
valid_to
edited_by
If valid_to = 9999-12-31 23:59:59 then that's the current record. If valid_to equals valid_from, then the record is deleted.
However, I was never happy with the triggers I needed to use to enforce foreign key consistency.
I can possibly avoid triggers by using the extension to the "PostgreSQL" database. This provides a column type called "period" which allows you to store a period of time between two dates, and then allows you to do CHECK constraints to prevent overlapping periods. That might be an answer.
I am wondering though if there is another way.
I've seen people mention using special historical tables, but I don't really like the thought of maintainling 2 tables for almost every 1 table (though it still might be a possibility).
Maybe I could cut down my initial implementation to not bother checking the consistency of records that aren't "current" - i.e. only bother to check constraints on records where the valid_to is 9999-12-31 23:59:59. Afterall, the people who use historical tables do not seem to have constraint checks on those tables (for the same reason, you'd need triggers).
Does anyone have any thoughts about this?
PS - the title also mentions auditable database. In the previous system I mentioned, there is always the edited_by field. This allowed all changes to be tracked so we could always see who changed a record. Not sure how much difference that might make.
Thanks.
