Interacting with a Strange Database

I relearned a couple of lessons today about working with database intensive applications.  Fortunately, I was merely writing test automation code and I caught my problems fairly quickly, but…

  • You should get the System DateTime.Now in a consistent manner.  It's either assigned by the database clock or the application server clock (assuming a cluster is synchronized somehow), but never both.  I got burned for a little while today because my system clock is apparently a few moments behind the database clock.
  • Don't play with strange databases.  If you're using a legacy database schema for the first time, assume nothing.  Treat them like a poisonous snake that needs to be handled with care.  Every legacy database seems to be littered with idiosynchracies just waiting to jump out at you.  That innocuous lookup table may be vital, referential integrity might be lacking, and there could be triggers lurking under the water waiting to take a bite out of developers swimming nearby.  I swear, and I know somebody is going to argue with me here, that the fastest way to make your code completely unmaintainable is putting business logic in triggers.  As much as I detest business logic in sproc's, at least you can generally see the flow of the code. 
  • Integrating through the database is not a good idea.  I've seen a handful of systems that integrate by having the downstream application determine it's workflow by scraping through the audit trail tables of the upstream application.  Everytime, the result is tears and brittle systems.  As an example, I'm thinking of designs where you might poll a table and look for any changes since the last time the polling job executed.
  • While convenient, using database tables to store application metadata can potentially cause you a great deal of friction in setting up the application for testing, especially when you're using a lot of surrogate keys (autonumber or sequence) for the metadata.  A single xml file is easier, and lends itself more readily to being versioned in source control than the corresponding storage in a dozen database tables.  At a bare minimum, have a way to suck in the database configuration from a single file.  If the application is hard to set up, test and build automation becomes very difficult and your velocity will suffer.

About Jeremy Miller

Jeremy is the Chief Software Architect at Dovetail Software, the coolest ISV in Austin. Jeremy began his IT career writing "Shadow IT" applications to automate his engineering documentation, then wandered into software development because it looked like more fun. Jeremy is the author of the open source StructureMap tool for Dependency Injection with .Net, StoryTeller for supercharged acceptance testing in .Net, and one of the principal developers behind FubuMVC. Jeremy's thoughts on all things software can be found at The Shade Tree Developer at http://codebetter.com/jeremymiller.
This entry was posted in Database and Persistence, Ranting. Bookmark the permalink. Follow any comments here with the RSS feed for this post.
  • ulab

    Donot depend on sp_helptext. It does not work. They ship it because they can.

    if you suspect something is changing your data and you seem not be able to reason it, do regex on the table name in your source tree… Because it can be from any other database.

    Ah, be careful for sprocs which have not been checked in your source tree …

    I have apps for this – the only way to do this is to search on the text of the sproc retrieved from the db.

    Consider the fact that may be it is not done. I looked for a bug for days and found that there was nothing which not doing it (rather than something not doing it correctly).