When should a database table use timestamps?

Question

First a note, I thought maybe this question belonged in the database exchange, but I think it is more broadly related to a programming solution as a whole than to databases. Will move to database exchange if people think thats the best one.

I was wondering when a database table should have a created and updated timestamp added?

The first obvious answer is that if any business logic needs to know when something was updated (like a transaction completion date etc) then it must go in.

But what about non business logic cases? For example I can think of scenarios where it would be really useful to know the date time that rows changed to help with fault finding e.g. some business logic is failing and looking at the related database rows its possible to identify that one row is being update before another row which is causing the error.

With this use case, it would make sense to give every table an update and create timestamp (except for maybe the most trivial enum tables that wouldn't be updated by any part of the application).

Giving every table a timestamp is surely a great way to quickly bog down a database (although could be wrong).

So when should a database table use create and update timestamps?

Answer 1

For a better and more comprehensive database management and most wise practice is to do so.

First, it is more likely as a developer, you would wanted to have track on the database transactions and/or activities for development and ease on tracing bugs and errors on your code whenever it involves your database.

Also, whenever you need to track on the activities made on you database for statistic purposes.

Another, is it often happens that maybe for the time being you don't need to keep track of your database activities, but it is more likely you would in the future. It will be needing your time today, but buys you more in the future.

Answer 2

As someone who has been both poacher (developer) and gamekeeper (DBA), I'm surprised many still don't see the value in this and consider it bloat.

Simply put:

For any table where records are added (but never updated) e.g. logins etc I'd consider adding a DATE_CREATED column.

For any table where records are added and updated, I'd consider adding a DATE_CREATED and a DATE_UPDATED column.

I've worked at many places where DATE_CREATED and DATE_UPDATED are included in every table by default as part of the design.

For larger databases with millions/billions of rows where the database update ran over the course of a few days, we also added a SOURCE column for some tables which tracked which data pot caused the update e.g. 3rd party feed, user update, DBA modification, data cleanse etc.

Answer 3

The way the question is worded, you're asking for a list of things. I'm going to risk not directly answering your question, but answering when you should use an alternate solution.

I can think of scenarios where it would be really useful to know the date time that rows changed to help with fault finding

Would it be more useful to have a log of all the updates for a given record? Just knowing the last update, may not be enough information. This log could be put in a separate table. It would be more convenient to track changes from several tables in the same log file(s) (it doesn't have to be a table). This prevents some massive union query of all table change_dates to get aggregates. This would also benefit trouble-shooting by helping you see a recording of more events in your system.

In Addition: You have to consider the users as well. They may not make it a business case, but when you have inexperienced users or those in a corporate culture where they never make a user error and want to always blame it on the computer, any kind of logging will help including update dates on tables. In this case, you may want to have an Update_UserID field as well.

Answer 4

A database table should include creation and modification templates when either of the following is true:

1. The table represents a primary record of some user supplied activity. If the user does X , and you have both a Table_X and a Table_Y which are one-to-many children of Table_X, Table_Y is not a primary record and so doesn't need the extra fields.
2. When you have a permanent, temporary, or recurring need for system tracking. If you have a need to check that Table_Y only gets updated when Table_X is updated, the extra tracking fields can assist.

Note that neither of these are exclusive; you can go ahead and add them everywhere by default, and omit only when needed for performance tuning.

Answer 5

Personal opinion:

I don't see the value in a modified column.

created, absolutely, should be added to every database table unless there's an exceptional justification to not do it. There's so much value in having it there.

However, updated seems a waste. Why not just go the whole hog, make two database tables, one that specifies a document ID and another the document version. In a very simplistic case

create table document (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

create table version (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    document_id INT NOT NULL REFERENCES document(id),
    content TEXT NOT NULL,
    created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Then select the latest version of the document you want. This way, not only do you save every modification date - not just the last one - but you also keep every version of that document. The only argument against it really is hard drive space, but surely when you get to the point when you're bothered about what hard drive space it's using up - in most cases you'd be even more bothered about versioning the data