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?