by Jo K. Horan
Last month, in the second article in this series on database design, we created some simple database structures and described the types of relationships that could exist between different kinds of tables. Before we go much further, let’s draw a few pictures to illustrate exactly what we mean.
The first table we discussed was called “MEMBERS” and could have looked something like the example shown in Figure 1.
MBR_NBRLAST_NMFIRST_NMPHONE
=======================================
M001JeffersonThomas123-4567
M002WashingtonGeorge234-5678
M003FranklinBenjamin345-6789
M004LincolnAbraham456-7890
Figure 1. Sample MEMBERS table
These fields are all character fields, since they have letters as well as numbers, or special characters such as the hyphen. Address fields should also be character fields, even 5-digit zip codes, since you never do arithmetic on those numbers. You could include other items, such as a “dues paid” date field, and if you’re tracking dollar amounts for dues you would use a numeric field for that, so you could format the numbers correctly and add them up to get current totals.
The “member number” field is the key field for this table. It
guarantees that each record is unique, even if names are alike. (You could
have a problem with John Adams and John Quincy Adams if you didn’t
allow room for middle names.) And the “member number” field
serves another purpose, as well. It becomes the link to the second table
in our example, the one we called “OFFICERS”. Its layout would
resemble the format shown in
Figure. 2.
POSITIONELECTED MBR_NBRNICKNAME
=======================================
President 07/01/1997 M002George
Secretary 07/01/1998 M001Tom
Treasurer 07/01/1997 M004Abe
Figure 2. Sample OFFICERS table
This table uses “position” as its key field, and includes other information about each officer, including the name they like to be called by. But the rest of the data about these officers is kept in the “MEMBERS” table and referenced through the “member number” field. No information is duplicated, and if anything changes (such as area codes, for example!) it only has to be updated in one place. You can link “OFFICERS” to “MEMBERS” to look up the correct phone number when someone needs to know how to contact one of the officers, and you can link “MEMBERS” to “OFFICERS” to include each officer’s “position” when you print out your club’s directory. For members who are not officers, no link exists, so their “position” fields would just remain blank.
These two tables show how a simple “one-to-one” link can connect different sets of data in convenient, flexible ways. Now let’s go one step further, to see how useful a “one-to-many” link can be. Last month’s article assumed that you needed to maintain a list of committees for your organization. This “COMMITTEE” table would look something like the example in Figure 3.
COM_NBRCATEGORYCHAIRMANMEETING
=======================================
C001MembersM00509/14/1998
C002FinancesM00710/12/1998
C003EducationM01211/09/1998
Figure 3. Sample COMMITTEE table
This table’s key field is the “committee number”. We could have used “category” as the key field here, but instead we decided to create a unique code number. Yes, we used the “position” field as the key in the “OFFICERS” table, but we weren’t using that field for any other purpose. Here, we need a way to link each committee to the people that are on it, so a short code field is a better choice than the longer “category” field would be. Again, you’ll need a “one-to-one” link to “MEMBERS” for each committee chairman’s name and phone number, so that you can call that person prior to their next meeting to remind them to call all their committee members. And here’s where the “one-to-many” link comes into play. You’ll need to set up a new table called something like “PEOPLE” to list all the members of all the committees, but in a very efficient way. Then you can link the “COMMITTEE” table to the “PEOPLE” table, and “PEOPLE” to “MEMBERS”, and you’ll have what you need. This new table will resemble the one shown in Figure 4.
COM_NBR MBR_NBR SINCE
=======================================
C001 M006 07/01/1998
C001 M010 07/01/1998
C001 M008 03/01/1998
C002 M009 05/01/1998
C002 M011 05/01/1998
C003 M014 06/15/1998
C003 M013 02/15/1998
Figure 4. Sample PEOPLE table
You can see that the same “committee number” occurs more than once in this table, since each committee has several members. Because of this, the table needs a second field that can be combined with the committee number to create a “composite” key. That’s what the “member number” field does. Taken together, these two fields uniquely identify each row in the “PEOPLE” table, and this would hold true even if the same person is on more than one committee. (That’s called a “many-to-many” relationship, which we’ll talk more about next month.) We’ve included another field called “since” that indicates how long each person has been a member of each respective committee, just to show you what kind of information is appropriate for this type of table. And, as before, a “one-to-one” link on “member number” from the “PEOPLE” table to the “MEMBERS” table will give you access to the names and phone numbers of all the members of each committee, without duplicating any of that data.
Now let’s briefly summarize all these concepts. Our first two tables, “MEMBERS” and “OFFICERS”, used a one-to-one link on “member number” to connect the officers with their names, phone numbers and other data. The next table, “COMMITTEE”, listed committee categories and meeting dates and identified each committee chairman, but by “member number” rather than name. Again, a one-to-one link on this number from “COMMITTEE” to “MEMBERS” provided access to each chairman’s information, without any duplication. (Here we intentionally used different field names for the linking key field, but we kept the data identical in both tables.) And the fourth table, the one we called “PEOPLE”, used a “composite key” made up from the “committee number” and “member number” fields, so that we could have a one-to-many link from “COMMITTEE” to “PEOPLE” as well as a one-to-one link from “PEOPLE” back to “MEMBERS”.
It it’s starting to sound a bit confusing, it’s only because there’s a lot of new terminology to wade through all at the same time. Taken separately, these concepts aren’t that bad. So if you have any questions, or if you’d like a demonstration of how all this works together, plan to join us at HAL-PC headquarters for the Alpha Four / Alpha Five SIG meeting at 6:30 p.m. on Monday, September 14th. We’ll show you some active examples of these different types of database relationships, and try to explain why certain configurations have proved to be better than others. You can apply whatever you learn to any of the good database software products, since these database design concepts are not unique. We’ll hope to see you on September 14th!
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.