19

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?

|improve this question
  • 2
    I think you already answered the question yourself. The only answer one can give is "It depends on the scenario". – Philipp Jan 29 '14 at 9:41
  • 3
    In practice I have timestamps on nearly every table (mostly for the reasons you mention). As far as I can say this has no negative effects on performance, at least for the type of databases that are commonly used in web development with maybe some 30.000 articles and hundreds of thousands of orders (which need timestamps anyway). There may be edge cases, but for example our ERP system (Microsoft Navision) hase those timestamps on most tables, too. – thorsten müller Jan 29 '14 at 9:42
  • 2
    You say Giving every table a timestamp is surely a great way to quickly bog down a database, but you don't say why. In almost every DBMS, a timestamp is a very small value - usually 8 bytes or less. Unless you add indices, that's negligible. – Ross Patterson Jan 29 '14 at 12:11
  • Updating timestamps because there's a change smells to me. It would mean you would have only the time of the most recent change to a record, what you want in business is to have a history of all changes. – Pieter B Jan 29 '14 at 12:49
  • @PieterB There is definitely value in keeping history for some tables but I've never come across a case where you'd want to do this for every table - YMMV. – Robbie Dee Jan 29 '14 at 13:23
5

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.

|improve this answer
15

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.

|improve this answer
6

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.

|improve this answer
  • +1 This too is a common technique which can be employed via table triggers to throw a record into a history table which can then be delta'd. Some RDBMSs (e.g. Oracle's Flashback feature) also support the use of point in time queries where the state of the data at some point in the past can be inspected. – Robbie Dee Jan 29 '14 at 13:12
  • would a simple solution be to save any query that updates and table to a log? – Gaz_Edge Jan 29 '14 at 13:33
  • That is another way although it might become unwieldy for tables with a high volume/frequency of updates. Making it an external table could head off some of the problems though... – Robbie Dee Jan 29 '14 at 13:50
1

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.

|improve this answer
0

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

|improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.