I was asked to design a history functionality for my database, which means that every record should be stored with a timestamp, comment (created, edited, deleted) and a user.
In my eyes there are 2 possibilities:
1) Store all entries in the same table, and add created and deleted column. Then I could use an insert- trigger to mark the current row with deleted is null.
- no extra tables needed
- very huge tables
- have to deal with history relevant aspects (affects keys have to add where deleted == null to every query). For the querys I Could use views only to get the relevant data but the axtra columns for the design remain.
2) Use an extra Table
- clean tables
- Performance: I don’t have to query for the current entry (although deleted == null should be fast), only when the user wants to see the history
- Many tables: As my database has to deal with many languages there is always a details_table to each original table to store language relevant data. So for example I have Person and Person_Details and I would need Person_History, Person_Details_History
- Maintenance: Any changes has to be made in the original and the History table
- Redundant data: Anytime when the user adds an entry I would copy the entry to the history table and add the history relevant information
I am not so happy with both solutions. I started with approach 1) but had to realize that my database is too complex. I already had composite keys or keys consisting even of 3 columns and a lot of relations. So due to complexity I can only work with extra tables.
I counted 19 details – tables, but I guess I have no other chance than creating 19 history tables for that.
Any comments or hints to speed up the history table creation process are highly welcome.