So for testing, DBAs have their SQLIO, performance monitor and a host of other monitoring tools. It helps them gauge how well hardware and networks are holding up.
And also for testing, us Database Developers have SQL Server DMVs, Server Traces other tools. It helps gauge how often and how hard hitting queries are. Pointing to missing indexes, stats, or maybe a query or sproc in need of a rewrite.
But how do Database Designers test their designs out? I think I’ve come up with something that I’ve found useful. It tests one DB design rule of thumb that I’ve always gone by.
The DB-Design Rule of Thumb
Databases should only store data that follows your business rules.
Well duh! But what I mean to say is that a database should not be allowed to store data that doesn’t follow business rules right? In other words you should never overhear anyone say “That bug is due to bad data in the database.”
It’s that rule-of-thumb that I’m I want to test here.
So here’s what you do:
- Find your favorite database test data generator
- Point it at your favorite development database. (Operational tables only, skip the static data tables).
- Load that database with as much data as you can.
- Use the app (in my case a web site)
- Debug the crazy stuff but (and here’s the crazy part) as db design bugs!
Don’t think of these bugs as problems on your way to a nice test database. They’re opportunities to improve your database design! Let me explain it the way the kids do on the internet:
Realizing the glass is half full.
The crazy stuff is really helpful in pointing out what your database will allow but shouldn’t allow. The stuff that can be debugged usually falls under two categories:
- Actual application exceptions.
- App behavior that makes you go hmmm*
Bonus:
This method actually works best on applications that have been around a while. I tried this method out on a large old application (considered very mature in terms of software age) and in the first ten minutes I found a missing foreign key and two missing constraints and a large number of columns that should not allow nulls.
Bonus #2:
This isn’t SQL Server specific, but works for any well normalized (or nearly normalized) RDBMS.
Caveat:
First of all. Everyone should know that I consider myself more of a database developer. I’m competent at database design, but I’m kind of wading into territory normally occupied by Louis Davidson (Twitter|Blog) or Karen Lopez (Twitter|Blog). If either of them weigh in here, or anywhere for that matter, listen to them.
Cheers, Let me know if you found any of this useful at all. I’d like to see what mileage others get.
* — BTW, anyone else miss the nineties yet?
