If you haven’t seen the comments from yesterday’s editorial, there is some really good insight. AZ Jim provides a good case for requirements first design in a Waterfall methodology. If you are doing waterfall development, he has it right on. Requirements, logical model, physical model, begin coding. This works extremely well, and has proven success to the same degree of any other methodology.
Personally, I love an environment with a lot of chaos, defined by a rapidly changing database design. This isn’t for everybody, and it requires a LOT of discipline to get it right. Here are activities you must do to have the same degree of success as a waterfall design.
- Automated, continuous database creation populated with real data. The database is created from scratch from the same master files committed to version control for your database, and all of it’s objects. A complete, brand new database can be created by executing a build program.
- Automated application of database changes. This is the same as taking a database already created in step one above, identifying, and applying all changes that have been created and not yet applied. This allows you to modify an existing database to conform to the latest structure and objects without loss of data.
- Database unit testing. You have code in your database in the form of functions, triggers, and stored procedures
Technically, views could be considered testable too. Regardless, you need to have unit tests for your procedural code in order to confirm that database changes (migrations) have not broken your code.
With these tools in place you can quickly change things when you find you need a new column for something you didn’t dream up a week or month previously. Using this technique I usually develop multiple things simultaneously. My process is as follows.
- Update the database diagram.
- If the diagraming tool can’t generate SQL, modify the master file myself, otherwise script a new master file from the ERD tool.
- Create a change script. I like to use fluent migrator, because it is open, it tracks version change, it knows what has and has not been applied, it performs as an executable, and therefore can be shipped, it can work with embedded SQL, SQL script files, or use fluent language to implement the necessary changes. Fluent migrator can also be easily executed from your build machine, so Continuous integration is not restricted to application code. Now it keeps the database in lockstep with your application design.
- Modify, if needed, data conversion scripts for transforming old databases. If I am transforming data in place, that is addressed in step three.
Believe it or not, this really isn’t that different from the waterfall method. You still have logical design, physical design, implementation, conversion, and testing tasks. The difference is that you do them in stages, instead of for the entire database.
Because of developing in stages, it MUST have the above capabilities. The reason is you are going to get something wrong. You get it wrong in waterfall too as AZ Jim stated. The difference is, in Agile you may discover the issue earlier, and you have opportunity to address it more often. When you do get it wrong, your tests will tell you when you break something trying to fix it, and your automated systems will implement your new design quickly for yourself, other developers, and other environments such as QA. When you’re done, deployment to production is a piece of cake, because you have done it frequently since the project started.
If this seems a little bit out there for you, check out some of the great books on Agile database methods. There’s a few on Amazon. Scott Ambler’s books have helped me d
Cheers,
Ben