10 Common SQL Mistakes - dummies

By Allen G. Taylor

Face it — nobody studies SQL for the fun of it. You use SQL to build database applications, but before you can build one, you need a database. Unfortunately, many projects go awry before the first line of the application is coded. If you don’t get the database definition right, your application is doomed. Here are ten common database-creation mistakes that you should be on the lookout for.

Don’t assume that your clients know what they need

Generally, clients call you in to design a database system when they have a problem getting the information they need because their current methods aren’t working. Clients often believe that they have identified the problem and its solution. They figure that all they need to do is tell you what to do.

Wrong. Most users don’t possess the knowledge or skills necessary to accurately identify the problem, so they have little chance of determining the best solution.

Your job is to tactfully convince your client that you are an expert in systems analysis and design and that you must do a proper analysis to uncover the real cause of the problem.

Don’t ignore project scope

Your client tells you what he or she expects from the new application at the beginning of the development project. Unfortunately, the client almost always forgets to tell you something — usually several things. Throughout the job, these new requirements crop up and are tacked onto the project.

If you’re being paid on a project basis rather than an hourly basis, this growth in scope can change what was once a profitable project into a loser. Make sure that everything you’re obligated to deliver is specified in writing before you start the project.

Don’t consider only technical factors

Issues of cost maximums, resource availability, schedule requirements, and organization politics can have a major effect on the project. These issues may turn a project that is feasible into a nightmare. Make sure that you understand all relevant nontechnical factors before you start any development project.

Don’t avoid client feedback

Your first inclination might be to listen to the managers who hire you. After all, the users sure as heck don’t pay your fee. On the other hand, there may be good reason to ignore the managers, too. They usually don’t have a clue about what the users really need. Wait a minute!

Don’t ignore everyone or assume that you know more than a manager or user about how a database should work. Data-entry clerks don’t typically have much organizational clout, and many managers have only a dim understanding of some aspects of the work that data-entry clerks do. But isolating yourself from either group is almost certain to result in a system that solves a problem that nobody has.

You can’t always use your favorite development environment

You’ve probably spent months or even years becoming proficient in the use of a particular DBMS or application development environment. But your favorite environment — no matter what it is — has strengths and weaknesses.

So rather than kludge together something that isn’t really the best solution, bite the bullet. You have two options: Either climb the learning curve of a more appropriate tool and then use it, or candidly tell your clients that their job would best be done with a tool that you’re not an expert at using.

Then suggest that the client hire someone who can be productive with that tool right away. Professional conduct of this sort garners your clients’ respect. (Unfortunately, if you work for a company instead of for yourself, that conduct may also get you laid off or fired.)

Don’t exclusively use your favorite system architecture

Nobody can be an expert at everything. Database management systems that work in a teleprocessing environment are different than systems that work in client/server, resource sharing, web-based, or distributed database environments. Choose the best architecture anyway, even if it means passing on the job. Not getting the job is better than getting it and producing a system that doesn’t serve the client’s needs.

Don’t design database tables in isolation

If you incorrectly identify data objects and their relationships to each other, your database tables are likely to introduce errors into the data and destroy the validity of any results. To design a sound database, you must consider the overall organization of the data objects and carefully determine how they relate to each other. You must determine what is appropriate, considering your client’s present and projected needs.

Don’t neglect design reviews

Even the best designer and developer can miss important points that are evident to someone looking at the situation from a different perspective. Presenting your work before a formal design review can make you more disciplined in your work. Have a competent professional review your design before you start development. You should have a database designer check it over, but you may want to show it to the client, also.

Don’t skip beta testing

Even if you test it in every way you can think of, the application is sure to contain failure modes that you don’t uncover. Beta testing means giving the application to people who don’t know how it was designed.

They’re likely to have problems that you never encountered because you know too much about the application. You can then fix the bugs or performance shortfalls that others find before the product goes officially into use.

Don’t forget to document your process

If you think your application is so perfect that it never needs to be looked at, even once more, think again. The only thing you can be absolutely sure of in this world is change. Count on it. Six months from now, you won’t remember why you designed things the way you did, unless you carefully document what you did and why you did it that way.

Over-document your work. Put in more detail than you think is reasonable. It will pay off later.