Ten of the Biggest Mistakes Developers Make With Databases, Page 2
What a Great Place to Hide Application Logic!
Stored procedures and triggers are a wonderful thing. When you've got multiple clients accessing a database, they can be a great way to make sure consistent data processing takes place. But they can also turn into an ugly black box in which application logic hides, unknown to Web and thick client developers, generally unseen and unreviewed. Too often database code isn't subject to the same standards of design, test, and code review that we demand for the rest of our applications. When you're tempted to put code in the database, take a moment to ask yourself whether it really belongs there.
Who Needs Backups?
Who needs backups? You do. Presumably you're storing data in a database because it's important enough to hang on to. Somehow, though, I end up walking into situations where "nobody got around to it" on a regular basis, and valuable data is lost forever because hardware, hackers, or just plain mistakes munged the database and there wasn't any backup. Your backup plan (including things like frequency, type of backup, and how often you're going to take backups off-site) needs to be in place at the start of the development cycle, not at the end.
Yes, You Need Version Control
Speaking of backups, you need to worry about schema changes to your database as well as data changes, and you need to keep track of those schema changes in such a way that you can recreate the database at any point in time. That's right, if you want to do a really professional job of building software you need to extend version control to your database design. It doesn't do much good to be able to recover version 0.784.5 of the software to test out a customer bug if you can't also produce the corresponding database. If your database developers are cheerfully writing stored procedures and tweaking table designs without leaving any trace of their work, you've got a problem.
Use the Tools
Modern databases offer a lot more than just a series of buckets that you can toss your data into. They also come with a substantial variety of tools to make it easier to manage that data. For example, SQL Server makes it easy to inspect the plan of attack that the server is using for your queries, and even includes wizards to tell you what indexes would make your queries more efficient for the actual load you've been throwing at your server. I've had great success running these tools on client databases and speeding things up, or lowering CPU usage by a factor of two - but the fact is, they shouldn't have had to call in a consultant to tell them to use the stuff in the box. If you don't know what tools and utilities come with your database, and what they can do for you, then you're paying for value that you're not receiving.
Don't Assume Everything is a Nail Just Because You Have a Really Big Hammer
Databases have a tendency to take over all data storage for an application. I've seen applications that tried to build an entire metadata-driven user interface, and then stored that metadata along with user preferences in the same database that was holding the business data. This is a good way to complicate your life and kill performance; some data really does belong in local files, not in a client-server database across the network. When you're storing data, you need to evaluate the different places you can put it (database, registry, plain text files, XML files...) and pick the appropriate spot for each piece of data. Don't just automatically shove it into a database just because you have a connection string handy. These days, there's probably more of a tendency to overuse XML files than relational databases, but the principle still holds.
About the Author
Mike Gunderloy is the author of over 20 books and numerous articles on development topics, and the Senior Technology Partner for Adaptive Strategy, a Washington State consulting firm. When he's not writing code, Mike putters in the garden on his farm in eastern Washington state.
# # #