last_update timestamp for every table?

Question

Is it a good practice to have timestamps for the last row update in every table?

Or should they only be set where really needed? How do I know where I will need an update timestamp later? For which kind of information do they make sense?

Answer 1

Its a better decision to keep it in the tables used to capture transactions. It might work for logging/security or might be used to handle concurrency.

Answer 2

row timestamps are used for row versioning in situations where you have optimistic record locking; i.e. 2 users editing the same record at the same time.

You can detect if the DB version has changed, by examining the current row timestamp with the timestamp found when you retrieved the row from the DB.

Answer 3

I think the real question is what you would want to use those timestamps for. If your application has no actual need for it, then I don't see a strong case for storing and maintaining that data.

Answer 4

I think it's a slippery slope to start adding "last_updated" timestamps to tables. So you might have people asking "when was this row changed?" but as soon as you add a last_updated column, they're going to ask, "OK, it was chnged 6 days ago. What was changed?". So now you have to set up a "history" table with all of the columns that changed. But then they ask, "Well, the 'foo' column was changed 6 days ago, what was the previous value?" And now you're keeping track of the complete history of every row in the entire database!