2

I am under the impression that having created_at or updated_at columns seems to be a best practice in database design. Frameworks like "Ruby on Rails" and "Laravel" both support that.

I know what they are and I guess one main use case is when my data is corrupted I can check these columns to find out when that happened. But I also feel that is more like in theory because I definitely have other ways to find out why and when the data is corrupted.

So can someone cast some light on the benefit of having them in database design? Maybe it can be explained with some example?

8

Columns like these should not be added because "the framework makes it easy" or because of some cargo-cult "best-practice" illusion. They should be added in case there is a real requirement, a real use case for them, something requested by a user or stakeholder.

For example, in a customer database, a company may keep track about their customers and business partners as well as their contact persons, by manually entering the data from several sources like mail, email, phone calls and information from the web. Unfortunately, such data tends to age over time and loose its currentness. So they may have a process to validate all customer data which hasn't been updated in a period of more than 3 years, for example. And I guess it is pretty be self-evident how columns like "created_at" or "updated_at" can be used to support such a process.

Or, lets say one implements a forum software or blogging system, or a system like Stackoverflow, where users can enter and edit their posts. In these systems, it is a usual requirement to keep track of when the posts where entered, or when they were changed last time, and show that information to the users of the system.

So in general, whenever a system manages data where it is important for the users to know the age or currentness of the data, introducing timestamp columns becomes necessary. And since in larger systems this can affect multiple tables and the implementation can often be made in a uniform way, it is not very surprising that certain frameworks provide support for this whole category of requirements.

| improve this answer | |
  • 3
    An extra reason it's often kept is because people usually only realize they need historical data after the thing they wanted to know about happened, so it's kept "just in case". And this one happens to be useful often enough that it's become a cargo-cult practice. – Erik Sep 3 at 8:44
  • 3
    @Erik: historical data requires a lot more than these time stamp columns, it requires to keep track of old records and not deleting them, and it requires any process in the system to distinguish between "old records" and "current ones". This is a huge topic and goes far beyond what was asked in the question, that's why I intentionally tried not to mention it. – Doc Brown Sep 3 at 8:48
  • 1
    Another use is for systems which require that processing moves from state A to state B, without "forgetting" about the update that happened between read and write. Forcing the requirement that an update to a record, not only match the guid, but also has the same original time stamps (and that these are changed post update) can allow optimistic locking in a massive parallel system. The long running pieces of work will know when they make their update that something changed. – Kain0_0 Sep 3 at 9:13
  • 1
    Not everyone appreciates a question which boils down to "because bob and alice did it". – Kain0_0 Sep 3 at 9:15
  • 1
    ... don't forget "who did what and when" is person-related data. In some environments, it is crucial to log this information. In some other environments, it could become quickly illegal. So what in some domains may be a "best practice" can turn out as a really bad practice in another domain. – Doc Brown Sep 3 at 13:33
3

Another use case is for systems requiring that processing moves from state A to State B without forgetting about any update that happened between the read and the write.

By combining some date stamps (start, and last update, perhaps even end-date if you desire soft-deletion) a process can determine if a record has been updated since it was last read.

This allows optimistic locking, where instead of obtaining a lock initially. The process instead reads the data, performs its work, and then when it goes to update the record first checks that the guid, and timestamps match while updating the record. If it determines that the record has changed it can respond by redoing its own work, or be making a correction to its own processing and trying again.

This is particularly useful for:

  • Code running in parallel that might be competing to update the same record
  • A process that cannot maintain a transaction (and database write lock on the record) for the duration of it's processing.
| 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.