Overkill to add last_modified column to all tables?

Question

When designing database tables for a fresh new project, is it good practice to add a last_modified column, which auto-updates, for every table?

Is the performance hit to have auto-updating last_modified columns too much to make it worthwhile?

This column could have a number of uses, one being to aid with caching. But is it overkill to just throw such a column into every table?

Also - are there other useful reasons to have a last_modified column?

This is specific to MySQL, as it's the only system I use.

Answer 1

It depends!

Do you need to know the last_modified date for each table? If so, then you need that column.

Cater for the domain, and then worry about performance, UNLESS you know that you will have an insane number of INSERTS/UPDATES on that table...

Answer 2

Well, I would say you should have that column for tables in which records are frequently changed by some user action. And on top of that, if you have authentication in place, it's useful to stored who made the changes. It may also save you some grief when someone goes to you asking who made some changes to a record because something is broken.

Answer 3

I have found this to be a particularly troubling piece of information. I only get when the record was modified, but not what was modified, or by who or why, or how.

I prefer to have audit tables for my important tables.

Answer 4

Why do you want it?

It will be a small performance hit on insert or update. It is useful on occasion if you need to send out information on records changed since a certain date (what we generally call a delta feed). It is not terribly useful for management of the data changes. Like Raj More, I prefer the audit table approach that stores the data of the change, the old and new value and who made the change. This can then be used to rollback a specifc change or group of changes which is pretty hard to do if you are relying only on backups to rollback bad data.

It is generally not useful at all in cases where the data is static or where it is renewed from a batch process (like an import) rather than the user interface. Why bother to have such a thing on lookup tables for instance?