Originally created 02/15/99

Who's in charge -- you or your database?



Your home computer bundle has two kinds of databases built into it.

First, there's a general purpose database included with Microsoft Works. While it may seem lightweight, it is conceptually identical to those giant and sometimes ominous-seeming systems that store your tax returns or checking account.

Then there's a slew of specialized databases: The various address books that are included with AOL or other e-mail packages, the Windows Phone Dialer and your home finance program are the most common.

You'll find lots of help for using Works in the built-in help system and templates. What we're going to deal with this week is the conceptual stuff.

All true databases are formal, highly structured ways of organizing information. Unless you're prepared to invest a fair amount of time in creating forms, entering data and maintaining them, you might be better off with whatever system you use now. Indeed, the most important first lesson about databases is when to use them, and when to leave well enough alone.

Take your home-finance program (either Intuit's Quicken or Microsoft Money). You can easily spend a hundred hours setting it up, and a couple of weekends a year maintaining and entering information about your finances. If you have few investments and have to write just a dozen checks a month, the effort's barely worth it. On the other hand, if you own stocks, have a 401(k) program, deduct business expenses on your taxes and write lots of checks, a good finance program can save you a lot of tedious check writing and calculating. (You might also want to look into whether your bank has "Web banking," which handles the basics of bill paying in a simpler, more reliable fashion than Quicken or Microsoft Money.)

Quicken is typical of databases that are set up to do one thing and one thing only. The Works database is much more flexible: You decide what goes into it, and how the material is organized. I've used a similar program for years to create Christmas card labels, and I've set up mailing-list systems for volunteer groups.

Databases, regardless of what they're used for, have these common traits: They are composed of "records," a term that has a very specific meaning in the context of databases. Each record is highly structured and identical in format. Think of a tax return, a check or a mailing label.

To be useful, each record in a database must be unique. Your January phone bill is different from your February phone bill, if for no other reason than the date is different.

For my Christmas card list, I used the name of my best friend in each family to establish a unique record. But the bigger the list gets, the harder it is to keep each record unique -- to keep John Doe No. 1's records from tripping over John Doe No. 2's. That's why we have Social Security numbers -- they represent a unique code that government computers use to distinguish you from me. And that's why checks are numbered, so each is unique.

Each record is composed of fields. Think of fields as containers for specific types of information, including numbers and dates as well as text. These data types, as they're called, can be manipulated by the computer; for example, it can add a collection of number fields.

My Christmas card list has fields for last name, first name, middle initial, street number and name, city and ZIP code. I also include the field "family name," which is how the name will appear on my mailing labels.

Creating a database has three stages.

First, you design the database, deciding what information you want to collect, the labels of fields, what data will be in each field, and whether and how you'll perform computation operations. Then, you enter information into the blank records. And finally, you output your data, for various uses: display, as with mailing labels, or analysis, as with reports that show all the checks you've written to your plumber in one year.

Once you've entered data into a database, you usually can display it in different forms. In Microsoft Works, for example, you can enter your data into a grid that looks something like a spreadsheet, with each record assigned its own row and several records visible at a time. Or you can create onscreen forms that display only one record at a time. Similarly, you can output your data as the pages of a phoneaddress book, as mailing labels or as a simple list. You can even vary the size and typeface of labels.

Maintenance is a continuing problem: How do you make sure the data are up to date? A checking account program on your computer isn't going to do you much good when you're writing a lot of checks on the road. An electronic address book isn't terribly accessible in a phone booth. When you update a phone number on your computer in the office, how do you make sure it's updated at home? What about e-mail addresses: How do you get them out of the database and into your e-mail without retyping?

These kinds of mundane problems make databases -- which are utterly indispensable for businesses and volunteer groups -- surprisingly useless around the house. I've never been able to come up with a single database to manage all my addresses and phone numbers, nor do I expect to. About the closest I've come, in recent years, is to throw most of the important stuff onto a Web page, which I can view from any computer in the world.

That's a solution that's increasingly feasible with the advent of so-called "portal" pages: Most allow you to set up custom calendars and address books that are stored on host computers on the World Wide Web. You can get at these databases from any computer, and you can share them with others as well. You'll find a good collection of portal links on my home page, www.dolinar.com.

The best way to get around these problems is to use databases targeted to do one narrow task well, rather than try to create a monster that will organize your whole life and everyone in it.

Because specialized databases include less information, less of it will drift into obsolescence each month. Take the Christmas card list, the oldest home-computing cliche in the book and the perfect example of why it is best to keep things simple and specialized. Like a lot of people, I started my mailing list by using a canned template from a Works program. It had a field for titles, family members' names, birthdays, addresses, phone and fax numbers, and so on.

After spending the better part of a day entering data on 40 families, I realized I was wasting my time: There was no way to write rules on how to combine the various fields into meaningful names for my Christmas card list. Sure, Mr. and Mrs. John Doe works fine in most cases and can be set up as a combination of fields. But how do you automatically format a mailing label to a household in which both parental units have kept the last names they used prior to marriage?

Do you address the card to John Doe and Mary Roe? What if they're John Doestenkowski and Mary Roestenkowsi and thus do not fit on the same line of the mailing label? What if they have a kid? John Doe, Mary Roe and John Jr. isn't bad, but what about John Doe, Mary Roe, Curly, Larry, Moe and Shemp, which not only sounds stupid but fails to fit on a label.

I finally gave up and created an extremely simple mailing label database. The first two fields are the last name and first name of the key contact in the family. Whenever we need to search through the database, this is the obvious way to do it. I set the labels so that these two lines are visible only on the data-entry screen, not on the final mailing label. The third line on the screen is the first line that prints out: It is whatever I've decided is the best way to address the family -- the Roestenkowskis, or whatever -- and I create it manually for each entry. The rest of the fields -- street number and name, town, state, ZIP -- go below. That's it -- no dossier of birthdays, beeper numbers or beach-house addresses.

Now this is a perfectly sensible approach, which is why it is more obvious to people who don't use computers than to those who do. In 15 years of writing about computers, I have yet to see a predesigned database that is set up this way.