History for all Entries: Extra history table or same table with composite primary key

[wuz]

Hello everybody,

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.

Pros:

- no extra tables needed

Cons:

- 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

Pros:

- 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

Cons:

- 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.
Big thx!

[timothybard]

I have used history tables for several database I have programmed. Fortunately, I was aware of the need for the history tables when I started the database, so I created them as I created the original tables. The history tables I use are identical to the original tables except I replace the ID field with a field called OriginalID and I add a new primary key to the history table. I track the user who updated the data and the date the data was updated on the original table.

In order to create the history tables, you can just copy and paste the structure of the tables and add a few fields or you can do it programatically. The number of history tables will probably determine which you want to do.

I agree that it can be a pain to update a table since you need to update the history table as well.

As far as redundant data... separate history tables are not any more redundant since, in your first option, you have several records with the same data except for the data that was updated and which one is current.

Lastly, if you keep your original tables in one database and keep all your history tables is a separate database and just link to the data from the frontend, then you can allow your history table database get bloated without your original data database being bloated.

If you are only required to keep the history records for a certain amount of time, then you are delete the old records to keep the database size down.

For my cases, the history files are rarely needed, so if the file does get bloated, it doesn't matter as much since I only access it when there are issues.

Some other options concerning the history files... if you don't need all the history records at your finger tips, you can backup the old history records into their own database so that the "active" history database stays small. You can also put each history table in its own database so that no single history database is that large.