Is having "standard" columns in your database tables common and good practice? or is this overkill?

Question

Our database currently has "CreatedByUser", "CreatedTime", "ModifiedByUser", "Modified Time", "Status" and "Description" for all the tables in our database. Sometimes it is used correctly but for the most part, it is not. Is this a standard practice or is this totally overkill? If it is standard practice, is there an easy way to automate the creation of "standard" columns for every table you create in Microsoft SQL Server Management Studio? Thanks in advance.

Update: to answer the question, "not used correctly" meaning either it is not populated or not updated at all for the modified fields.

Answer 1

If the audit requirement is to track who created and/or modified each record in the database, then yes, this is good practice.

Insert statements should be set to use "current timestamp" for the createdTime and modifiedTime.

Update statements should keep the prior createdTime and use "current timestamp" for the modifiedTime.

Application code should handle which user is involved.

Answer 2

The answer depends on necessity* based on the requirements of the stake holders and your experience. For any type of information that will be subject to:

• security audits
• data recovery
• testing

add appropriate* logging columns. Even if there is no present need to perform these tasks, if in your experience*, they end up being done anyway, add the columns to be safe*.

*:How you interpret the starred depends on the importance of the application, the amount of resources required to maintain this info, or anything else that could come into play for your particular scenario.

Answer 3

Totally dependent on your application. I can see the utility of the CreateBy/ModifyBy fields, but what are "Status" and "Description" used for, in a general sense?

I'm hesitant to recommend this (I'm not a huge fan of triggers), but instead of relying on application code to manage and update these fields, you could create database triggers to update the ModifyTime and ModifyUser columns.