Jeremy D. Miller -- The Shade Tree Developer

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.