10 Big Mistakes Developers Make with Databases
   By Mike Gunderloy

Databases have been around for a long time, yet developers are still making the same database mistakes that should have been renovated long ago. Here are the first five of the 10 biggest offenders and how to stop making them—now.

Mistake #1: Choosing the Wrong Database
Not all databases are created equal — which means before you do anything you have to pick the right one. There are three tiers of databases in the market these days:

• Desktop and embedded databases, which are suitable for smaller tasks.

• "Express" versions of the key competitors that are good up to a few gigabytes of data.

• The truly valiant databases like SQL Server, Oracle and DB2, that can manage just about anything.

Before you do anything else, you need to make some realistic estimates about the amount of data that you'll be storing and pick the appropriate product to do the storage.

Mistake #2: Choosing Too Many Databases
Some APIs have promoted the notion of database independence – the idea that you can write your application code in such a manner that you can access any database for data storage. When you're starting out with a new product, pick your storage engine and write to it. If your product is good, people will install the database you specify and you won't be wasting man-hours supporting emergency scenarios that you'll probably never need.

Mistake #3: You Don’t Know Your Data
Database design can't be done in a vacuum away from the business rules. It's critical that you get the input of the actual users of the data. They need to tell you exactly how big each column needs to be, what rules apply to it, what types of data it will hold, who can update it and so on. Without all this imperative information, you're setting yourself up for costly rework down the line.

Mistake #4: If You Know One Database, You Know Them All
There's a tendency to assume that any developer knows how to set up a database. This results in many databases being designed by people who have never even heard the term normalization, let alone developed any understanding of the various normal forms. Efficient database design is something you need to learn, not discover by trial and error. Get the training you need now and save yourself the headache later.

Mistake #5: Taking Your Normalization Knowledge to the Extreme
A little knowledge can be a dangerous thing. There are some well-meaning developers who insist on putting everything in lookup tables in their databases. You need to know the normalization rules, but you also need to develop the skill to know when to stop normalizing and when denormalization for performance actually makes sense.

Mistake #6: Putting Code Where It Doesn’t Belong
When you've got multiple clients accessing a database, stored procedures and triggers can be a great way to make sure consistent data processing takes place. However, they can also be an area in which application logic hides, unknown to Web and thick client developers. 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, ask yourself whether it really belongs there.

Mistake #7: Backups? What Backups?
Who needs backups? You do. Presumably you're storing data in a database because it's important enough to hang onto. When hardware, hackers, or just plain mistakes manipulate the database, valuable data is lost forever because 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.

Mistake #8: Non-existent Version Control
Speaking of backups, you need to worry about schema changes to your database as well as data changes. You also need to keep track of those schema changes in such a way that you can recreate the database at any point in time. This means you need to extend version control to your database design. If your database developers are writing stored procedures and tweaking table designs without leaving any trace of their work, you've got a problem.

Mistake #9: You Don’t Know How to Utilize the Tools
Modern databases come with a substantial variety of tools to make it easier to manage the data. Unfortunately, some of these extras are so complicated that developers are calling in consultants to tell them how to use the equipment 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.

Mistake #10: Putting Data in the Wrong Place
Databases have a tendency to take over all data storage for an application. When you're storing data, you need to evaluate the different places you can put it (database, registry, plain text files, XML files, etc.) and pick the appropriate spot for each piece of data. Don't automatically deposit it into a database because you have a connection string at hand. These days, there's probably more of a tendency to overuse XML files than relational databases, but the principle still holds the same.


---Source: Mike Gunderloy, senior technology partner at Adaptive Strategy (www.developer.com).

To test your database knowledge and ensure your data hygiene, check out our Data Quality Tools Suite