What are the pros and cons of having history tables?

Question

WHAT is the better practice?:

1. Keep history records in a separate history table

2. Keep history records in the active table with different status?

In my opinion I rather to keep a separate table to avoid creating one huge table with duplicate records which may cause unwanted lag time when querying the table.

Answer 1

My preference has always been to have a separate table with history, purely because it removes the need to have a "WHERE Status = 'LIVE'" or "WHERE CurrentRecord = 1" to get latest record (I won't get into one design that required an inline query to get max(version) to get the latest). It should mean that the current records table should remain smaller and access times may be improved, etc. In the worst case scenario, I've seen an ad-hoc query against a table pick up the wrong version of a record, causing all sorts of problems later on.

Also, if you are already getting the history from another table, you could shard the data, so all history from one year is in one table/db and all history from another is in another table/db and so on.

Answer 2

I would go for a separate table, otherwise setting up UNIQUE and FK constraints may be still doable, but too involved.

Answer 3

Pro:
If you keep the history in a separate table then this data will be accessed only when you need to search something from the past. Most of the times the main table will be used far more than the historical one. So this means faster results.
Con:
In a project I worked, I had one table with 350 columns (don't ask why.....). So this table became very large as the data was inputed. At a specific moment records went from 'active' to 'closed' status. I was tempted to move all the closed records to a new table (a historical one), but I realized that it was more slow - in a lot of queries I had to make unions....

As a final opinion I think it depends for every case, but I will always think first for the separate table.

Answer 4

I prefer to use one table and partioning. I also would set up a view for the active records and use that instead of the base table when querying active records.