Does it make sense to standardize including a created date and last updated date field on all DB tables?

Question

My boss is currently attempting to apply some development standards to our team, so we had a meeting yesterday to discuss the standards which was mostly going well until she brought up:

• All DB tables will have a CreatedDate and LastUpdatedDate column, updated by triggers.

At this point our team suffered an opinion schism; one half of us think that doing this on all tables is a large amount of work with little benefit (we work on fixed-budget projects so any cost comes from our company's profits); the second half believe it will help with support of the projects.

I am firmly in the former camp. While I appreciate that some outside cases would cause the extra columns to improve supportability, in my opinion the amount of work that would be required to add the columns in the first place, as well as maintenance, would cause us to spend less time on more important things like Unit- or Load-Testing. Also, I'm fairly sure that these extra columns would make it more awkward to use an ORM - bearing in mind that we mainly use C# and Oracle, which isn't very ORM-happy to start with.

So, my question is twofold:

• Am I in the right camp? I don't claim to have world-renowned database skills, so this might be a trivially easy addition with no adverse side-affects.
• How would you deal with a situation where a meeting about standards devolves into a slagging match? How can I really sell that this standard is not going to help us in the long term?

Answer 1

This is a fairly common practice, although I wouldn't say supportability is the main benefit. The real benefit for this approach is keeping an audit trail. It's also common place to have an extra column containing the username of the user who made the last update.

If you're dealing with any kind of financial or sensetive data, I'm sure you've heard of things like PCI & SOX compliance. Having a comprehensive audit trail is essential in meeting those specifications..

Disclaimer: There are however, much better ways of achieveing a database audit trail > https://stackoverflow.com/questions/1051449/ideas-on-database-design-for-capturing-audit-trails

Answer 2

The former argument is invalid, because adding a few database maintained timestamp fields to a series of tables is not difficult work. This is in fact the kind of mind numbing task that one would give to a junior or an intern, and they could easily do it in a single two week sprint with time to spare.

It may or may not even be necessary to map these fields in your ORM, simply because you do not want application users to modify these fields and because they are useful for maintenance and debugging and rarely have use in business logic. I worked in shops that did it both ways and I frankly don't have much of an opinion on this either way.

The benefits, even if exagerated still far outweigh any human costs in implementing such functionality at the database level, and certainly probably less than the collective brain power of the projects great technical minds hijacking the meeting and sparring it out in an epic chest thumping match. When you tally up the impact that a few 1 hour meetings have on the lifespan of a project you probably won't be surprised that they are expensive. Imagine the collective hourly wages and benefits of all of those people combined and that should give you an idea.

Answer 3

... the more definitive statements a man makes the more likely he is to be definitively wrong ... - tyler durden

this applies to blanket "standards", while on some tables this could be a huge win, on every table it would most likely be useless noise and more code to maintain or forget to maintain.

there is a balance to be had here, that is what you should push to the decision makers.

Answer 4

I agree wholeheartedly. Almost every table in every database should have at least 2 fields: the creation date and update date. There are many reasons that you should put a create date and update date. For obvious reasons that prior people stated…which is audit.

I have been designing systems and databases for 25 years and have worked for hundreds of clients. There is not a single client that did NOT need this.

There are 2 basic ways to do this:

1 - The first practice is letting database do the work and put it directly into the table design. Which is the bare minimum, I would recommend.

2 - The other practice, which I prefer....is using a replication tool to handle this. There is little overhead and no cost to DEV teams. However the tools are expensive. One additional advantage is the delete process can be audited much easier with this type of tool. Without a replication tool you would need to create an audit table and fire triggers for deletes - not a good practice in my opinion.

Another benefit to having these fields is the data warehouse and ODS that is ALWAYS built for any OLTP system. You cannot effectively pull incremental data without it. Otherwise you risk having to reload the entire DB every day.

There is an enormous number of other business reasons for putting in these 2 dates, which I will not delve into here. Do your homework and I’m sure 3-6-12-48 months down the road you will be very happy you put in these 2 simple fields.

I have implemented and usually recommend both solutions where possible.

Answer 5

We have the created date and created by columns in our database and they have helped us tremendously in tracking down data issues. If we need to revert, it helps us to find the correct records in the full audit tables (because we know where to look in a very large table). She should add a created by and modified by columns too. It really really helps to know who put the data in especially if you don't have full auditing.

I can think of no Enterprise application which doesn't need auditing of some form or another. Apparently your boss thinks it needs only relatively mild auditing. Personally I favor full auditing on every database that contains data your company is dependant on (it's a whole lot easier to revert those 2000 bad records from audit tables than to restore backups) and would require it if there is any financial information at all as I have seen this type of thing help catch people committing fraud. All auditing must be at the database level.

How can this data help? Well first it narrows down when to look to find the old data (in a revision) and it can help you see what version of your program was active at the time the data was entered. So if you know you fixed that problem in version 2.3 which went live on 6 July 2011 and then found the same problem with a record inserted on Aug 7, then maybe your fix wasn't good. If you need to revert to old data it will tell you what version of the backups you can find the old data in if you don't have full auditing.

Developers rarely seem to think that data must be maintained over time and that bad data needs to get fixed by someone. Having things like that can be very valuable to those of us who have to do such things. Your boss is right, although I don't think she went far enough in auditing. It only takes one really serious problem that is easy to fix to justify the very small amount of time it will take to add these columns and triggers.

Answer 6

The workload is moot because this can be scripted and applied to every database you'll ever create. Add the columns to all tables along with the triggers. You just have to remember to run it with your build.

As far as what the client wants, you can have them pay you to integrate them in your app as they see fit. Many like to see additional information on a record like who created/changed it last and when. No need to send everyone an email to find out or be lied to. You don't want to have to query a log every time someone looks at a record.

Putting it in the database and having it there just in case isn't that difficult and may allow you to charge for additional features that utilize the fields or just to give you some feedback on how much clients are using the system.

Answer 7

This would be a fairly trivial to implement (maybe 1 to 3 days total), so in my opinion its how much value its going to add to your application over its lifetime.

First, an alter table statement would be needed to add the columns, the alter table would all be the same (except for the table name), so you could write a script to code generate the alter SQL statement for all the tables this is needed. Have to allow for NULLs to account for existing data and check for the existance of the columns so that it is re-runnable.

Second, for the columns, using default values, like GetUTCDate() (SQL Server, Oracle maybe different) solves any coding additions on insert, so the code base doesn't have to change for any of the inserts statements since default values will be used.

The updates to data (change to last modified) could be solved with an update trigger. Again, this trigger would be nearly the same across all tables, so this trigger code (SQL) could be code generated as well for any existing tables.

There would be potentially a lot of sql script code (depending on how many tables), but it a pattern that is repeatable, so you could code generate it by looking at an existing DB schema.