Types of Relationships

  Previous section   Next section

Before you begin to establish relationships between tables in the database, you must know what types of relationships can exist between a given pair of tables. Knowing how to identify them properly is an invaluable skill for designing a database successfully.

There are three specific types of relationships that can exist between a pair of tables: one-to-one, one-to-many, and many-to-many. The tables participate in only one type of relationship at any given time. (You'll rarely need to change the type of relationship between a pair of tables. Only major changes in either of the table's structures could cause you to change the relationship.)

Note

The discussion for each type of relationship begins with a generic example of the relationship. Learning how to visualize a relationship generically enables you to understand the principle behind the relationship itself. Once you understand how and why the relationship works, you'll be able to determine whether it exists between a given pair of tables quite easily.

Each discussion also includes an example of how to diagram the relationship. I provide special instructions pertaining to the diagramming process where appropriate and explain the symbols incorporated within the diagram as necessary. This allows you to learn the diagramming method at a reasonable pace and keeps you from having to memorize the entire set of diagram symbols all at once.

Figure 10.2 shows the first symbols you will use to diagram a table relationship.

Figure 10.2. Diagramming symbols for a data table and a subset table.

graphics/10fig02.gif

One-to-One Relationships

A pair of tables bears a one-to-one relationship when a single record in the first table is related to only one record in the second table, and a single record in the second table is related to only one record in the first table. Figure 10.3 shows a generic example of a one-to-one relationship.

Figure 10.3. A generic example of a one-to-one relationship.

graphics/10fig03.gif

As you can see, a single record in TABLE A is related to only one record in TABLE B, and a single record in TABLE B is related to only one record in TABLE A. A one-to-one relationship usually (but not always) involves a subset table. Figure 10.4 shows an example of a typical one-to-one relationship that you might find in a database for an organization's human resources department. This example also illustrates a situation where neither of the tables is a subset table.

Figure 10.4. A typical example of a one-to-one relationship.

graphics/10fig04.gif

Although the fields in these tables could be combined into a single table, the database designer chose to place the fields that can be viewed by anyone in the organization in the EMPLOYEES table and the fields that can be viewed only by authorized personnel in the COMPENSATION table. Only one record is required to store the compensation data for a given employee, so there is a distinct one-to-one relationship between a record in the EMPLOYEES table and a record in the COMPENSATION table.

A one-to-one relationship usually (but not always) involves a subset table. (Indeed, neither of the tables in Figure 10.4 is a subset table.) Figure 10.5 shows a generic example of how you create a relationship diagram for a one-to-one relationship.

Figure 10.5. Diagramming a one-to-one relationship.

graphics/10fig05.gif

The line that appears between the tables in the diagram indicates the type of relationship, and there is a particular line that you use for each type. Later in this chapter, you'll learn how to modify the line to show the characteristics of the relationship as well. Figure 10.6 shows the relationship diagram for the EMPLOYEES and COMPENSATION tables in Figure 10.4. (Note that a Data Table symbol represents each table.)

Figure 10.6. The relationship diagram for the EMPLOYEES and COMPENSATION tables.

graphics/10fig06.gif

One-to-Many Relationships

A one-to-many relationship exists between a pair of tables when a single record in the first table can be related to one or more records in the second table, but a single record in the second table can be related to only one record in the first table. Let's look at a generic example of this type of relationship.

Say you're working with two tables, TABLE A and TABLE B, that have a one-to-many relationship between them. Because of the relationship, a single record in TABLE A can be related to one or more records in TABLE B. Figure 10.7 shows the relationship from the perspective of TABLE A.

Figure 10.7. A one-to-many relationship from the perspective of TABLE A.

graphics/10fig07.gif

Conversely, a single record in the TABLE B can be related to only one record in TABLE A. Figure 10.8 shows the relationship from the perspective of TABLE B.

Figure 10.8. A one-to-many relationship from the perspective of TABLE B.

graphics/10fig08.gif

This is by far the most common relationship that exists between a pair of tables in a database, and it is the easiest to identify. It is crucial from a data-integrity standpoint because it helps to eliminate duplicate data and to keep redundant data to an absolute minimum. Figure 10.9 shows a common example of a one-to-many relationship that you might find in a database for a video rental store.

Figure 10.9. A typical example of a one-to-many relationship.

graphics/10fig09.gif

A customer can check out any number of videos, so a single record in the CUSTOMERS table can be related to one or more records in the CUSTOMER RENTALS table. A single video, however, is associated with only one customer at any given time, so a single record in the CUSTOMER RENTALS table is related to only one record in the CUSTOMERS table.

Figure 10.10 shows a generic example of how you create a relationship diagram for a one-to-many relationship.

Figure 10.10. Diagramming a one-to-many relationship.

graphics/10fig10.gif

Note that the crow's foot symbol is always located next to the table on the "many" side of the relationship. Figure 10.11 shows the relationship diagram for the CUSTOMERS and CUSTOMER RENTALS tables in Figure 10.9.

Figure 10.11. The relationship diagram for the CUSTOMERS and CUSTOMER RENTALS tables.

graphics/10fig11.gif

Many-to-Many Relationships

A pair of tables bears a many-to-many relationship when a single record in the first table can be related to one or more records in the second table and a single record in the second table can be related to one or more records in the first table.

Assume once again that you're working with TABLE A and TABLE B and that there is a many-to-many relationship between them. Because of the relationship, a single record in TABLE A can be related to one or more records (but not necessarily all) in TABLE B. Conversely, a single record in the TABLE B can be related to one or more records (but not necessarily all) in TABLE A. Figure 10.12 shows the relationship from the perspective of each table.

Figure 10.12. A many-to-many relationship from the perspective of both TABLE A and TABLE B.

graphics/10fig12.gif

This is the second most common relationship that exists between a pair of tables in a database. It can be a little more difficult to identify than a one-to-many relationship, so you must be sure to examine the tables carefully. Figure 10.13 shows a typical example of a many-to-many relationship that you might find in a school database, which happens to be a classic example of this type of relationship (no pun intended!).

Figure 10.13. A typical example of a many-to-many relationship.

graphics/10fig13.gif

A student can attend one or more classes during a school year, so a single record in the STUDENTS table can be related to one or more records in the CLASSES table. Conversely, one or more students will attend a given class, so a single record in the CLASSES table can be related to one or more records in the STUDENTS table.

Figure 10.14 shows a generic example of how you create a relationship diagram for a many-to-many relationship.

Figure 10.14. Diagramming a many-to-many relationship.

graphics/10fig14.gif

In this case, there is a crow's foot symbol located next to each table. Figure 10.15 shows the relationship diagram for the STUDENTS and CLASSES tables in Figure 10.13.

Figure 10.15. The relationship diagram for the STUDENTS and CLASSES tables.

graphics/10fig15.gif

Problems with Many-to-Many Relationships

A many-to-many relationship has an inherent peculiarity that you must address before you can effectively use the data from the tables involved in the relationship. The issue is this: How do you easily associate records from the first table with records in the second table in order to establish the relationship? This is an important question because you'll encounter problems such as these if you do not establish the relationship properly:

There are two common methods that novice and inexperienced developers use in a futile attempt to address this situation. I'll demonstrate how you might apply these methods using the STUDENTS and CLASSES tables in Figure 10.16 as examples.

Figure 10.16. Structures of the STUDENTS and CLASSES tables.

graphics/10fig16.gif

Note

As this example unfolds, keep in mind that every many-to-many relationship you encounter will exhibit these same issues.


As you can see, there is no actual connection between the two tables, so you have no way of associating records in one table with records in the other table. The first method you might use to attempt to establish a connection involves taking a field from one table and incorporating it a given number of times within the other table. (This approach usually appeals to people who are accustomed to working with spreadsheets.) For example, you could take the STUDENT ID field from the STUDENTS table and incorporate it within the CLASSES table structure, creating as many copies of the field as you need to represent the maximum number of students that could attend any class. Figure 10.17 shows the revised version of the CLASSES table structure.

Figure 10.17. Incorporating STUDENT ID fields within the CLASSES table structure.

graphics/10fig17.gif

This structure is likely to be problematic, so you might try taking the CLASS ID field from the CLASSES table and incorporating it within the STUDENTS table structure instead. Figure 10.18 shows the revised version of the STUDENTS table structure.

Figure 10.18. Incorporating CLASS ID fields within the STUDENTS table structure.

graphics/10fig18.gif

Do these structures look (vaguely) familiar? They should. All you've done using this method is introduce a "flattened" multivalued field into the table structure. In doing so, you've also introduced the problems associated with a multivalued field. (If necessary, review Chapter 7.) Although you know how to resolve a multivalued field, this is not a good or proper way to establish the relationship.

The second method you might attempt to use is simply a variation of the first method. In this case, you take one or more fields from one table and incorporate a single instance of each field within the other table. For example, you could take the CLASS ID, CLASS NAME, and INSTRUCTOR ID fields from the CLASSES table and incorporate them into the STUDENTS table in order to identify the classes in which a student is currently enrolled. This may seem to be a distinct improvement over the first method, but you'll see that there are problems that arise from such modifications when you load the revised STUDENTS table with sample data.

Figure 10.19 clearly illustrates the problems you'll encounter using this method.

Figure 10.19. The revised STUDENTS table with sample data.

graphics/10fig19.gif

Fortunately, you will not have to worry about any of these problems because you're going to learn the proper way to establish a many-to-many relationship.

Self-Referencing Relationships

This particular type of relationship does not exist between a pair of tables, which is why it isn't mentioned at the beginning of this section. It is instead a relationship that exists between the records within a table. Ironically, you'll still regard this throughout the design process as a table relationship.

A table bears a self-referencing relationship (also known as a recursive relationship) to itself when a given record in the table is related to other records within the table. Similar to its dual-table counterpart, a self-referencing relationship can be one-to-one, one-to-many, or many-to-many.

One-to-One

A self-referencing one-to-one relationship exists when a given record in the table can be related to only one other record within the table. The MEMBERS table in Figure 10.20 is an example of a table with this type of relationship. In this case, a given member can sponsor only one other member within the organization; the SPONSOR ID field stores the member identification number of the member acting as a sponsor. Note that Susan McLain is Tom Wickerath's sponsor.

Figure 10.20. Example of a self-referencing one-to-one relationship.

graphics/10fig20.gif

Figure 10.21 shows how you diagram this type of relationship.

Figure 10.21. Diagramming a self-referencing one-to-one relationship.

graphics/10fig21.gif

One-to-Many

A table bears a self-referencing one-to-many relationship to itself when a given record in the table can be related to one or more other records within the table. Figure 10.22 shows an example in which a given customer can refer other customers to the organization. The REFERRED BY field stores the customer identification number of the customer making the referral. Note that Paul Litwin referred both Andy Baron and Mary Chipman.

Figure 10.22. Example of a self-referencing one-to-many relationship.

graphics/10fig22.gif

Figure 10.23 shows how you diagram a self-referencing one-to-many relationship.

Figure 10.23. Diagramming a self-referencing one-to-many relationship.

graphics/10fig23.gif

Many-to-Many

A self-referencing many-to-many relationship exists when a given record in the table can be related to one or more other records within the table and one or more records can themselves be related to the given record. This may sound somewhat confusing at first, but the example in Figure 10.24 should help clarify the matter.

Figure 10.24. Example of a self-referencing many-to-many relationship.

graphics/10fig24.gif

In this case, a particular part can comprise several different component parts, and it can itself be a component of other parts. For example, a clamp assembly (Part ID 704) is composed of a fastening bolt (Part ID 703), a bottom clamp (Part ID 702), and a top clamp (Part ID 701). Additionally, the clamp assembly is itself a component of a seat assembly (Part ID 707) and a frame assembly (Part ID 711). Figure 10.25 shows how you diagram this type of relationship.

Figure 10.25. Diagramming a self-referencing many-to-many relationship.

graphics/10fig25.gif

Note

Before you begin to work through the examples in the remainder of the chapter, now is a good time to remember a principle I presented in the introduction:

Focus on the concept or technique and its intended results, not on the example used to illustrate it.

There are, without a doubt, any number of ways in which you can relate the tables in these examples (and in the case study as well), depending on each table's role within a given database. The manner in which I use the examples here is not important; what is important are the techniques I use to identify and establish relationships between tables. Once you learn these techniques, you can identify and establish relationships for any pair of tables within any context you may encounter.


Now that you've learned about the various types of table relationships, your next task is to identify the relationships that currently exist among the tables in the database.


Top

Part II: The Design Process