How to store table with a large number of fields?

What is the best design for an audit/history table?

• There are three approaches, but I am not sure which one should be followed, A single table with the fields id | table | column | row | old_value | new_value | timestamp | userid. This would track all changes to all tables in a single place and has the benefit of minimizing the number of tables. It does make querying a little difficult, but not impossible. Multiple tables like #1 except without the table column. This would separate the changes from each table into their own history table. Multiple tables that mirror the schema of the original tables to track. This would make the triggers a lot easier to write, would make restoration of the data easier if someone wanted to revert to a specific record, but would come at the expense of storage, as every field, even if it hadn't changed, would be duplicated, possibly multiple times. Also, it would make it difficult to know specifically which fields changed from one version to the next.

• Answer:

  Option #3 is almost always the best one, at least for traditional structured databases. There are a number of reasons as to why: - Option #1 and #2 may not duplicate the entire row but they have considerable storage overhead that may exceed just duplicating the row. Remember, you are duplicating row headers, metadata (table, column, etc) for each value that changes in a single updated row. For many data models, you will not save much space this way and storage for things like audit tables is inexpensive because it is rarely accessed except to append. - If columns in a table have different data types, how are you storing all the values in the same table? You could do something like translating them text but that will be efficient for neither storage nor query. - Option #1 and #2 are more flexible in terms of schema changes but for #3 many databases will allow schema changes to be applied transactionally to both the main table and the shadow audit table simultaneously. However, you need to decide what should happen to the audit table if, for example, you drop a column in the main table. - In a number of databases (e.g. PostgreSQL), there are fast, general ways to copy a row to a shadow table with common data models. This is how auditing is often implemented in practice. #3 can be even faster than it appears. However, the most important reason is what you mention above: - Option #3 makes it very easy to query or rollback the history because it has the same data model as the original table. You can do simple SQL queries on the union of the live table and the audit table. Queries like "show me all the changes applied to this record over the last month" are simple and efficient to both write and execute. No row reconstruction from individual value changes is necessary. If you ever need to use the audit table, you will appreciate this. Note that the above assumes a traditional SQL-like database with a well-defined schema. If the database is more document-oriented (e.g. Mongo) then a mechanism like the Option #2 makes more sense.