Database records revision

Question

Hi, I have to questions concerning database records revision.

First of all I have database table with name and content columns. I want to have "backup" versions of the content (and maybe name) column saved plus the user, who made this edit.
Is it better to have an audit table with previous versions (and should I keep only the changed column or both) or save everything in one table and just put a timestamp in it?

Note: My content column is going to be a text field with not very small texts, which will be frequently changed.

And I would also like to ask you if it is going to become very difficult to make revisions of the table along with its "children" (to-to lists). I mean to keep revisions of both the to-do items and the previously mentioned table. Or is it going to be better if I somehow markup those to-do lists in the content column (will it slow down performance on read-write to-do)?

Thank you in advance!

Answer 1

In most cases it is better to have a separate History table, for the following reasons:

• querying the current version of the table is straightforward (and that's what is the most common active in a normal application);
• maintaining data integrity is easier (because unique constraints and foreign keys are simple);
• the performance of indexed reads won't degrade as much.

The one problem with the separate table approach is that it is harder to identify the version of a child record which belongs to a given version of a parent record. There is no straightforward solution to this. But the least worse solution is to filter on the basis of the version timestamps.

Answer 2

We always built another table with the same name but suffixed with "_audit", and two extra columns. A unique Id and a datetime. Then we built triggers on update and delete which put the old row in the history table.

That way, data which was write once would not take up twice as much space in the database, and we could write queries which determined what the data was like on a particular day.