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?
Best Answer
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.