Bottom up database development can save a lot of time and resources compared to a Top Down scrub process. Let me explain what I mean because you are probably asking yourself what I’m talking about.
By bottom up database development I mean that change begins in a development environment, and is pushed up to other environments in a re-producible manner. Table creation or modifications and procedural code all begin in your dev environment. A baseline of the dev database(s) is maintained that may be restored at any time in a continuous integration environment allowing constant testing and validation of your database modification process.
A top down process assumes that there is no baseline database available. In this scenario, backups from production database(s) are restored elsewhere, and the data is massaged enabling it to be used in a dev environment. Often times this requires a lot of code on the part of systems developers to gather the backup, restore it somewhere, scrub the data, maybe even shrink the database, and make it available for use elsewhere such as in a developer sandbox or replacing a dev environment.
Often the top down approach is used in order to generate enough useful test data to adequately exercise the applications. In a perfect world, the required data is instead created at the time the features are being developed, and it becomes a part of the baseline data.
Often there are scenarios where a nearly static baseline is not adequate, because data is time sensitive, and the application will not be adequately tested without modifying the timelines. Sometimes in this situation a script may be maintained to roll the dates for existing data to cause it to be current, allowing full application testing.
Do you use Bottom Up, Top Down or some other process to manage your testable database? There are some interesting tools such as NDBUnit, and many other retail products. Are you finding those tools to be useful? Share your thoughts on maintining testable databases by email to btaylor@sswug.org, or leave a comment here online.
Cheers,
Ben