Date: March 3rd, 2008

Author: Susan Harkins

Whether you take on freelance work or you support your organization’s IT needs by developing custom database solutions, you must build an efficient, easy-to-use database if you plan to stay employed. Like most things, there’s a right and a wrong way. You might not get credit for doing things right, but you’ll certainly hear about it if you do things wrong.

The road to the right database starts well before you start building tables. There are a number of things you can do before you build a database to make sure that the development process goes smoothly and that your custom database fits the organization’s purpose and goals. The following tips are aimed as Access users, but most of them apply to just about any custom database.

Note: This information is also available as a PDF download.

#1: Make nice

You’ll get nowhere without the support and guidance of two specific groups of people:

In a small company, one person might fill both positions, although they have different needs. However, that person’s experiences with the data are valid. It’s your job to find solutions that satisfy everyone, within reason.

#2: Bend but don’t break

Being just a developer won’t get the job done. Sometimes, you must be a diplomat. I recommend that you practice the art of persuasion: “Let me show you something…” will serve you better than “That can’t be done.” This may require you to think fast on your feet. Of course, “Let me work up an example” can always buy a little time.

#3: Actually review their specs

Sometimes, you get lucky and someone in-house supplies a list of specifications. If that happens, it’s information worth keeping, so don’t be too eager to trash the list. Working with those original specs will save you some time and might keep you from stepping on someone’s toes — never a good idea if you can help it.

#4: Compare the specs to the working environment

Most Access databases have just a few users, but Access can handle numerous users. You probably won’t build an interactive intranet database the same way you’d build the solution for a single user. Access seldom fails to meet the demands if you develop for multiple users from the beginning.

#5: How many keys are there to the front door?

Keeping hackers out of your intranet or Web-based database is much more complex than using Access’ workgroup security. In fact, if you need this article and you’ve taken on a Web-based database project, you might have bitten off more than you can chew — good luck! Access is certainly up to the challenge, but the truth is, most developers aren’t. That’s why IT professionals scoff at Access. The sad truth is, many developers don’t understand the Web. If you’re one of them, don’t take on a Web project hoping to learn on the job. You and your client will pay too high a price.

#6: Do the work

Sit down with the people who do the work and learn the process:

• Review all paper forms in the current process.
• How much data — both records and fields — will the database store?
• How much searching and sorting will the users require?
• Where does the data come from? Will the system need to accommodate foreign data?
• Will the system export data to foreign formats?
• Review the current reports and analysis. Talk with the people who use them, for insight.

In short, follow the data from beginning to end. There’s no substitute for knowing the data and the current motivations that push that data from collection to final form.

#7: Re-evaluate

Once you’re familiar with the specs and environment, you might have to shoot yourself in the foot. Access just might not be the best solution for your client. A more powerful system, such as SQL Server 2005 Express Edition, might be a better choice. Or Access might be just part of the solution. For instance, InfoPath’s XML-based forms or .NET forms might be more efficient than Access forms, especially if you’re publishing data to an intranet or to the Internet. Certainly, Excel’s analytical tools are superior to those Access provides. Don’t try to stuff the entire works into an Access-or-bust solution.

#8: Recommend the best route — not the easiest one

Don’t be afraid to suggest a major overhaul if you’re upgrading a legacy database. Neither the latest and greatest version of Access nor more expensive hardware will resolve performance issues that stem from bad design.

#9: Improve the process

Work with the end users to improve the manual process if there’s room for improvement, and there usually is. It’s a mistake to computerize the existing workflow until it’s the best it can be. Software alone won’t improve a bad routine — it’ll just change the problems.

#10: Define and redefine

Once you’ve gathered all your facts, compose a mission statement for the application. This might require one to several paragraphs. I’m not talking about a new set of specifications. Rather, give your client a realistic review of their needs versus reality. You’re simply restating the database’s purposes, but with the benefit of your insight into the workflow and organization’s needs.

#11: How’s that for quick response?

Once you believe you have a good feel for the client’s needs and the database’s purposes, create a series of mock-up forms to show the client. You’ll get a few oohs and ahhhs, but listen to the souls brave enough to say, “But wait…” Their insights may be valid and could save you some trouble down the road. On the other hand, this is where #2 can come in handy. Sometimes, people just can’t conceive of doing something any way but the way they know.

You can use graphics software to draw and print the forms or use Access — it’s a great rapid applications development (RAD) tool. And you can really impress your clients by actually using their data. Sometimes, a quick run at normalizing the data can help the mock-up process. It’s not strictly necessary, but it may show you some holes you might otherwise miss.

Susan Sales Harkins is an independent consultant and the author of several articles and books on database technologies. Her most recent book is Mastering Microsoft SQL Server 2005 Express, with Mike Gunderloy, published by Sybex. Other collaborations with Gunderloy are Automating Microsoft Access 2003 with VBA, Upgrader’s Guide to Microsoft Office System 2003, ICDL Exam Cram 2, and Absolute Beginner’s Guide to Microsoft Access 2003, all published by Que. Currently, Susan volunteers as the Publications Director for Database Advisors. You can reach her at ssharkins@gmail.com.