Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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?
share|improve this question
    
Why do you say C# is not ORM-happy ? Also, adding the [insert="false" update="false" generated="always"] properties to the mapping of these two columns in NHibernate for example does not seem that awkward to me, or am I missing something? –  Jalayn Nov 8 '11 at 10:39
    
C# + Oracle is not ORM-happy, and we found that NHibernate was too heavyweight (apparently, I wasn't involved in that bit of tooling investigation). I probably put the C# and Oracle backwards in the main question. –  Ed Woodcock Nov 8 '11 at 10:51
    
You should consider renaming the title of your question to be more descriptive to database standards. –  maple_shaft Nov 8 '11 at 12:41
    
How would this take time away from anything? You are going to have to do it at least twice for the 'outside cases'. Create a tool and some reusable classes and never worry about it again. –  Steve Evers Nov 8 '11 at 18:48
add comment

6 Answers

up vote 12 down vote accepted

This is a fairly common practice, although I wouldn't say supportability is the main benefit. The real benefit for this approach is keeping a rich 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 > http://stackoverflow.com/questions/1051449/ideas-on-database-design-for-capturing-audit-trails

share|improve this answer
    
Sorry, forgot to mention, PCI (etc.) compliance is not applicable, there is already process audit trail in the logs (EVERYTHING is logged quite thoroughly). –  Ed Woodcock Nov 8 '11 at 10:37
6  
"(EVERYTHING is logged quite thoroughly)" does that include CreatedDate and LastUpdatedDate? If so, perhaps you could point your colleagues towards the DRY principle :) –  MattDavey Nov 8 '11 at 10:39
2  
That's a very good point, perhaps I should be pushing for a more efficient log parser we can use to easily query the archived data (obviously the data is for audit trail purposes so we don't keep more than a week or so's worth available to query, the rest is warehoused). –  Ed Woodcock Nov 8 '11 at 11:09
1  
I don't think this approach will yield a rich audit trail... I wouldn't even call it an audit trail at all. –  Jordão Nov 8 '11 at 14:43
    
@Jordão I said it was a common approach, I didn't say it was a good one! Hence the disclaimer :) –  MattDavey Nov 8 '11 at 15:16
add comment

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.

share|improve this answer
3  
Create a script that will add these columns to every table if they don't already exist along with the triggers. –  JeffO Nov 8 '11 at 13:29
2  
+1 You could code generate the scripts easily in a few days. Only a lot of work if it is done manually. –  Jon Raynor Nov 8 '11 at 17:20
add comment

... 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.

share|improve this answer
add comment

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.

share|improve this answer
    
The clients have not expressed any opinion on the subject (as far as I am aware), and probably have no idea about our new standards push, so I would very much expect them to not be interested in paying for any integration ;) However, the "may allow you to charge" thing is a reasonably good argument, if a little reliant on "might" for my usual approach to development. –  Ed Woodcock Nov 9 '11 at 16:08
add comment

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. Apparantly 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 whch 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 audting. It only takes one really serious problem that is easy to fix to justify the very small amount fo time it will take to add these columns and triggers.

share|improve this answer
    
I'd prefer to see people effectively Unit Testing their fixes than attempting to verify them with DB checks, but I appreciate your point. However, I'm not sure that the point you make would apply to EVERY table in all our databases, even the reference tables etc. –  Ed Woodcock Nov 9 '11 at 16:14
add comment

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.

share|improve this answer
    
I'd worry that with a big-band approach like that you'd have long-term maintenance issues with new tables, or that (even worse) you'd have to make a sproc that scans each table for certain named columns then generates the DDL script to add them if missing, which sounds like a maintenance nightmare! –  Ed Woodcock Nov 9 '11 at 16:21
    
If this is a standard, hopefully the developer making the new tables follows the standard. Otherwise, yes, nightmare. The approach is to get the existing schema up to speed, afte that the onus is on the developer to follow the standard. –  Jon Raynor Nov 9 '11 at 17:37
    
I think the key word in that comment is "hopefully", I'm not sure I trust anything that has to happen of each new developer's own volition! –  Ed Woodcock Nov 9 '11 at 18:01
    
@Ed - Agreed, no trust, that's what code reviews are for! :) –  Jon Raynor Nov 9 '11 at 18:42
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.