A: The way you've structured your database tables -- with multiple fields for contact names within a customer record -- may work well for entering data, but as you've discovered, it makes it difficult to do even simple searching and reporting. Your data exists right now in one big "flat file." Each record in the table represents a customer, and you have five fields in which users can enter the names of contact people at that company.

The problems with a data model rarely reveal themselves as you set up your layouts and enter data. Indeed, it's easy to put those five contact fields on the customer layout and let users enter what they need. But what will they do if they have to enter more than five names for a given customer? That's the first issue with your data model -- it isn't extensible. Your database is imposing an arbitrary restriction on the number of contacts you can associate with a customer, and there probably isn't a business rule that requires such a restriction. You can, of course, add more fields and skirt any immediate problem, but you've merely raised the ceiling, you haven't eliminated it.

Another limitation will become evident the first time you want to search for a contact person. Say you have Mary Jones on the phone and need to look up the customer record of her employer. So you enter Find mode, and type "Mary Jones" … hmm, where? She might have been entered as Contact 1, or she may have been entered as Contact 3. You'll have to search all the contact fields. Yes, you can come up with a nifty script that searches them all, or concatenate all the contact names together and search the result, but those are merely work-arounds to make up for a deficiency in your data model.

Suppose for each contact you also have a checkbox that indicates whether the person is active or inactive. Your boss comes to you and asks you to print a list of all the active contacts. Or, maybe she wants something as simple as, say, mailing labels so you can send them all a new product announcement. As long as all the contacts are co-mingled with the customer record itself, you won't be able to find any subset of contacts, much less print them as a list or on mailing labels.

A more appropriate way to structure the data would be to have separate tables for Customer and Contact data. Assuming your customers all have unique IDs assigned to them (a primary key), you can then link Customers to Contacts in a one-to-many relationship simply by having a field in the Contact table where you store the ID of the Customer they're associated with (a foreign key). All the issues we described above disappear immediately: You can associate an unlimited number of contacts with a contact; there's no ambiguity as to where to perform a search; and finding and printing contacts is trivial.

"All fine and dandy," you say, "If I were able to start over from scratch. But how can I fix -- or work around -- my current database?"

Any solution begins with setting up a Contact table as we described above and creating a relationship between Customer and Contact. Assuming you've done that, then it's just a matter of writing a script to move the existing contact data from Customer into Contact. There are several approaches that will work. We'll look here at two simplified methods; you'll get the concepts and can then modify as necessary to accommodate your solution.