by Jo K. Horan
Last month, in the third article in this series on database design, we printed some examples that used typical data and simple codes to show relationships between different kinds of tables. One of these relationships was between one table called “OFFICERS” and another table called “MEMBERS”. These tables were linked one-to-one on a 4-character field called “MBR_NBR” which exists in both tables. The diagram in Figure 1 illustrates this link in a visual way, where the “parent” table is “OFFICERS” and “MEMBERS” is the “child”. By joining these tables, you can scroll through the list of officers to find the one you need to call, and from that person’s linked entry in the membership list you can select the right phone number. No data is duplicated and updates are easy and quick. If you want to reverse the relationship, so that you can include each officer’s position title when you print out your membership list, you can link the same two tables in the opposite way, as shown in Figure 2. Here, “MEMBERS” is the parent and “OFFICERS” is the child, but the “MBR_NBR” linking field is still the same.
These two examples show how “one-to-one” links can connect the same tables in opposite ways, in order to provide convenient answers to different types of questions. Our next example is also a “one-to-one” relationship, but with a slight difference. As shown in Figure 3, we want to use last month’s “COMMITTEE” table as the “parent” and link it to the “MEMBERS” table as the “child”. This link relates each committee’s chairperson to their correct member entry, so you can track each committee’s next scheduled meeting date, call the right chairperson, and remind them to call all of their committee members. The linking field is still “MBR_NBR” in the “MEMBERS” table, but the matching field is called “CHAIRMAN” in the “COMMITTEE” table. Different names for the linking fields are all right, as long as the data has the same format in both tables.
Now things start to get a little more interesting. In addition to knowing which person is in charge of each committee, you’d also like to keep track of each committee’s members. Last month we used a table called “PEOPLE” to match committee numbers with member numbers, and we included the date each member joined that committee, just as an example of appropriate data for this type of linking table. Since each committee has more than one member, the same committee number occurs several times in the “PEOPLE” table, once for each member of that committee. Now, in order to make use of this information, we need to create a “one-to-many” link from “COMMITTEE” to “PEOPLE” based on “COM_NBR”. This will allow you to remind each committee chairman just who the members are that need to be called for the next meeting. And, of course, each chairman wants you to rattle off all the committee’s current phone numbers, so that the chairman doesn’t have to look them up. So, to do this, we can set up another “one-to-one” link from the “PEOPLE” table to the “MEMBERS” table, based again on “MBR_NBR”. Now we have three different tables used in four separate ways and connected with two types of links, thus providing an easy way to accomplish those periodic phone calls. Figure 4 illustrates this arrangement.
So far, we haven’t discussed “many-to-many” relationships. This concept isn’t as difficult as many people seem to think it is. In fact, the “PEOPLE” table provides exactly what’s needed to expand the “COMMITTEE” listing to allow any member to serve on more than one committee. Just for an example, let’s assume that the club’s president has become an “ex officio” member of each of the club’s committees. Then, the sample “PEOPLE” table that we used last month will be lengthened by one additional member number entry for each committee. We now have a “many-to-many” relationship, because each committee has several members, and at least one member is on several committees. If you want to print a list of committees and their members, you can do so using the structure shown in Figure 4, which is still valid. And if you want to show which members are on what committees when you print your membership directory, you can link “MEMBERS” to “PEOPLE” to “COMMITTEE” as shown in Figure 5. Then committee information will be available for each member’s listing where it’s needed.
Now let’s review what we’ve covered. We linked the same two tables, “MEMBERS” and “OFFICERS”, in two different ways, using a one-to-one link on “member number” in each case. Linking one way accesses phone numbers and other data for each officer, while linking the other way identifies the officers where their names appear on the membership list. We also linked the “COMMITTEE” table to the “MEMBERS” table one-to-one, still using “member number” as a link but with a different field name in one case, to provide access to each committee chairman’s individual information. Then we expanded this structure to include a one-to-many link from the “COMMITTEE” table to the “PEOPLE” table, which contains committee numbers and member numbers for all the people on every committee. This table, in turn, is linked one-to-one to the “MEMBERS” table to provide names and phone numbers for each committee member. And finally, allowing for the possibility that any given person could be on more than one committee, we linked “MEMBERS” to “PEOPLE” using a one-to-many link, and “PEOPLE” to “COMMITTEE” one-to-one, in order to include committee assignments on the membership list. Used in these ways, the “PEOPLE” table provides a many-to-many link between the club members and the committees.
This shows you how flexible database systems can be. By keeping only closely related data in each table, and using concise codes to link separate tables together in different ways, you can use the same information to answer a variety of questions with a minimum of effort. There are many other examples that illustrate these various types of data relationships, and we’ll be happy to demonstrate some of them at the next Alpha Four / Alpha Five SIG meeting. Plan to join us at HAL-PC headquarters at 6:30 p.m. on the second Monday in October and bring any problems or sample information you’d like help with. We’ll show you how to organize your data so that any of the good database software products can make better use of it, whichever product you prefer. We’ll hope to see you on October 12th!
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.