Why every database table should have created/modified columns

This is a short and sweet post about those crucial 'created and modified' database columns that can be your saviour down the line for any project.

I'm sure almost everyone has worked on applications where the original developer missed adding a created or modified timestamp column on a database. There are many reasons for it - the developer forgot, ran out of time, or sometimes failed to realise the importance of time stamping every database record.

The simple rule is:

"Every table should have a column for "created timestamp" and "modified timestamp".

There are extensions to this in regards to having an 'owner' who modified and versioning history, but that is a far greater conversation.

The benefits you will get within your application:
  • You will always have a record of when the record was created.
  • You will always have a record of when the record was last modified.
  • It will dramatically speed up debugging of issues.
  • It provides necessary auditing information for your application.
  • Allows you to implement data archiving practices in the future.
What can happen if you don't implement:
  • You will have no idea when records were created or modified.
  • Makes debugging issues related to times and external error logs much harder.
  • You can't recover records based off date/times for legal purposes.
  • It will be near impossible to archive data in the future.
Gotchas:

  • Use a consistent method for timestamps. What this means is don't use native database timestamps in one area of your application (eg: 'NOW()' in SQL), and then use PHP timestamps in another area of your application (eg: new \DateTime()). This can cause inconsistencies if your application logic and database logic are on different servers in different timezones and not configured correctly. Use a single method (either PHP or in SQL) and stick with it.
Most frameworks (eg: Symfony2, Zend) provide events you can plug into to make implementation much easier. You can even look at creating an abstract class all of your entity classes extend that provide this core functionality.