Keeping the data in the database clean

On this new project we've designed a database from the ground up.  And we've done a lot of interesting design in the system, such as implementing one-to-one tables that mirror domain object inheritance in the actual system, and referencing a number of lookup tables.

There are no real data yet, so when we need to test something, it goes into the dev database pretty much via someone running an insert script.

Inevitably, we end up with bad data, data that doesn't match the design of the database, and don't show up on views that joins multiple tables.

In this area I've come to appreciate what my old company did.  They had heaps of views designed to catch bad data.

There's always a tiny part of my soul that says... but if you designed the database with proper constraints in the first place, you'd never have bad data in the first place.

May be our database constraints are too weak, but I suspect it's because we wanted the flexibility to define our data, and some of these flexible definitions can't be protected adequately with database constraints.

jliu