by Jo K. Horan
It always happens. No matter how well you design your database in the beginning, sooner or later you’ll have to make revisions. Circumstances change, new situations arise, or you begin to see other ways in which your database information could be useful. When this happens, the most important thing to remember is not to try to take short cuts. Study your new requirements as thoroughly as you analyzed your initial needs, make the appropriate changes, and you’ll be off and running again before you know it.
Here’s a typical example. In the third article in this series on database design, we showed a simple membership list consisting of member code, last name, first name, and phone number. As membership chairman for your group, you soon realize that you need both daytime and evening phone numbers for the people on your list. Obviously, the easiest thing to do is to change the PHONE field name to HOMEPHONE, insert a new field called WORKPHONE, maybe include another field called EXTENSION, fill in the data, and you’re through. Or so you think. But soon you may want to add fax numbers, pager numbers, mobile phones, and maybe even emergency contact numbers to the list. If you could guarantee that you’d always need exactly two and only two phone numbers for each person, perhaps adding a second field would work. But in the real world this seldom happens. And if you try this approach, and later discover that you’ve got to allow for more than two numbers per person, you’ll have twice as much work to do to fix the problem. It’s better to anticipate these changes and make the right corrections the first time.
The best thing to do is to create a separate phone number table and link this new table in a “one-to-many” relationship to the “MEMBERS” table, based on member code. This is easy to do. First, copy or export your original member code and phone number fields to a new table called “PHONES”, for example, using the existing field names. Then insert a new field named “TYPE” to distinguish between the different numbers for each club member. Here’s where good judgment is needed. You could use a single character, like “H” for “Home”, “W” for “Work”, “F” for “Fax”, and so on, but sometimes trying to be too efficient by saving a little space isn’t worth the effort. It’s probably better to make the “TYPE” field four characters long, so you can spell out a full word instead of using just an initial. This is easier to read and less likely to cause mistakes. Finally, either make sure that the “PHONE” field is long enough for the longest number, or add another short “EXTN” field for extensions. Figure 1 shows a simple example of what this new table might look like.
Now, link the new “PHONES” table to the original membership table based on member code, according to the method specified by your database software. Then, as a precaution, scan your new database (using either a “browse” or a “form” layout, the default version or a customized version if necessary), and compare each original phone number in the membership table to its corresponding entry in the new table. Once you’re satisfied that the numbers match, you can delete the “PHONE” field from the old table and start adding numbers to the new table as needed. If your database software lets you create lookup lists for specific fields, build a list that includes all the “TYPE” choices that you want to use, and you’ll simplify your data entry task and avoid typing errors. You may also be able to set up a format for the “PHONE” field to make sure that area codes are included. Now you can add as many phone numbers as you’ll ever want, and your database won’t have to be changed again to allow this to happen.
As you can see from this example, any given member number (which is always unique in the “MEMBERS” table) can occur one or more times in the “PHONES” table. This is what defines a “one-to-many” relationship. This was illustrated before in the same article when we linked the “COMMITTEE” table to the “PEOPLE” table, since each committee could have many members.
In that example we also mentioned needing to expand this into a “many-to-many” relationship between these two tables if any one person was allowed to become a member of more than one committee.
Figure 2 illustrates what the “PEOPLE” table might look like in this situation.
Each committee number occurs several times in this table, once for each person who belongs on the corresponding committee, and any of the member numbers can also appear more than once, if the person who matches that number is on more than one committee.
Using this concept, if we link “COMMITTEE” to “PEOPLE” one-to-many and “PEOPLE” to “MEMBERS” one-to-one (as diagrammed in the fourth article in this series), we can design a report like that shown in Figure 3 to list all the people who are assigned to each committee.
And if we invert this structure, linking “MEMBERS” to “PEOPLE” one-to-many and “PEOPLE” to “COMMITTEE” one-to-one, we can include committee assignments in our printed membership roster, as illustrated in Figure 4. (This relationship was also diagrammed in article number four.)
As you can see, the flexibility that we achieve through good database design is virtually unlimited. And once you understand the concepts, you can apply them to many different situations. For example, another common many-to-many relationship involves students and classes. Every class includes many students, and each student takes several classes. We’ll discuss all these examples in greater detail, and answer any questions you may have, at our next Alpha Four / Alpha Five SIG meeting workshop on the second Monday in December. Plan to join us at 6:30 p.m. at HAL-PC headquarters on December 14th and we’ll show you how easy it is for a well designed database to answer a wide variety of questions. We’ll look for you then!
Jo K. Horan is a database developer, consultant, and trainer, specializing in Alpha Four and Alpha Five. She is leader of the Alpha Four / Alpha Five Special Interest Group within HAL-PC, the Houston Area League of PC Users. She may be reached at 713-723-6709 or by mail at DataBasics, 9806 Cliffwood, Houston, TX 77096.
E-mail me at webmaster@hal-pc.org with any comments you have and tell me what you want to see here.