Establishing Each Relationship

   

Identifying Existing Relationships

When you were composing the table descriptions earlier in the database-design process (back in Chapter 7, to be exact), you assembled a representative group of users and management to help you with that task. These people were also designated as representatives of the organization and granted the authority to aid in the decision-making process throughout the remainder of the database-design process. (At least, this is the current assumption for the sake of discussion and example.) Now you'll arrange meetings with this group once again so that they can help you identify existing table relationships. These folks can provide valuable input because they are likely to have a good perspective on how various subjects (or tables) are related. Although their perceptions of the manner in which these subjects are related may not always be complete or accurate, their contributions will still be useful in identifying most of the relationships.

Begin the process of identifying relationships by creating a matrix of all the tables in your database. (You can do this on a sheet of paper, a white board, or a spreadsheet program.) For example, assume you're working with these tables:

BUILDINGS

FACULTY

STUDENTS

CLASSES

ROOMS

 

COMPENSATION

STAFF

 

List each of the tables across the top of the matrix, and then again down the left-hand side of the matrix; make certain the table names are in the same order. Figure 10.26 illustrates how the matrix should appear.

Figure 10.26. Setting up a table matrix to help identify existing relationships.

graphics/10fig26.gif

Select a table on the left as a starting point and determine whether it has a relationship with any of the tables listed across the top, working your way through the matrix as you do so. (It doesn't matter whether you work your way across the top or down the side. Just make sure you work consistently, as it will make the task much easier.)

Keep in mind that you're looking for direct relationships onlythere must be a specific connection between tables participating in the relationship. For example, the CLASSES table has a direct relationship to the STUDENTS table because one or more students can attend a given class. Conversely, the CLASSES table has an indirect relationship to the STAFF table via the FACULTY table; it is a faculty member that teaches a class, not a staff member. (You don't have to worry about indirect relationships just yet.)

As you work with a pair of tables, ask the participants questions about the records in each table. Your goal is to determine the relationship between a single record in one table to one or more records in the other table, and vice versa. (Remember that each record represents a single instance of the subject represented by the table.) When you get to a point where you're examining the same table on both sides of the matrix, try to determine the relationship between a given record in the table to one or more other records within the table.

There are two types of questions you can ask:

  1. Associative . This is a simple and straightforward type of question that you can generically phrase as follows : Can a single record in (name of first table) be associated with one or more records in ( name of second table)? Considering the matrix in Figure 10.26, you might ask an associative question such as this:

    Can a single record in CLASSES be associated with one or more records in BUILDINGS?

    You can use this type of question to determine whether a table has a self-referencing relationship by making two minor modifications to the question itself: Can a single ( singular form of the table name ) be associated with one or more ( plural form of the table name )? For example, here's a question you might pose for the STAFF table:

    Can a single staff member be associated with one or more other staff members ?

  2. Contextual . This type of question contrasts a single instance of the subject represented by the first table against multiple instances of the subject represented by the second table. There are two categories within this type of question: ownership-oriented and action-oriented .

    1. Ownership-oriented questions include words or phrases such as "own," "has," "is part of," and "contain." Here's an example of this type of question:

      Can a single order contain one or more products?

      You can use this question to test for a self-referencing relationship by making the same modifications you made to the associative question. Here's an example of a question you might pose for a PARTS table:

      Can a single part contain one or more other parts?

    2. Action-oriented questions incorporate action verbs such as "make," "visit," "place," "teach," and "attend." Here's an example of this type of question:

      Does a single flight instructor teach one or more types of classes?

      As you may have already guessed, you can use this question to test for a self-referencing relationship as well by making the same modifications:

      Does a single staff member manage one or more other staff members?

Use the type of question you believe to be the most appropriate for the pair of tables you're working with. As you work down the list of tables in the matrix, you'll eventually realize that you're asking questions about a given pair of tables twiceonce from the perspective of the first table and then again from the perspective of the second table. The answers to both of these questions will identify the type of relationship that exists between the tables.

Continuing with the example, assume that you've decided to start with the CLASSES table and this is your first question:

Is a single class held in one or more buildings?

The answer to this question will reveal the type of relationship that exists between these tables from the perspective of the CLASSES table . If you receive the following answer, then a one-to-one relationship exists between these tables:

A single class is held in only one building.

If you receive this answer, however, then a one-to-many relationship exists between the two tables:

A single class may be held in more than one building.

Once you've identified the relationship, indicate the relationship type in the box located at the junction of the CLASSES table row (on the left) and the BUILDINGS table column (on the top). You can use the following shorthand symbols for the relationship types:

1:1 one-to-one

1:N one-to-many

M:N many-to-many

Note

You won't need the many-to-many shorthand symbol at this point, but I've included it here for completeness.


Figure 10.27 shows how the table matrix looks after you've finished identifying relationships for the CLASSES table. Remember that the relationships indicated here are from the perspective of the CLASSES table.

Figure 10.27. Completed table-matrix entries for the CLASSES table.

graphics/10fig27.gif

You've probably noticed that some of the junction boxes are empty; this is perfectly acceptable. It's unnecessary for you to enter anything into the junction box if there is no relationship between the tables at either end of the junction.

Now you repeat this process for each table on the left-hand side of the matrix. Remember that you can start with any table. Let's assume that you decide to continue with the BUILDINGS table, and you're attempting to identify the relationship between it and the CLASSES table. Yes, I know you've covered this once already, but in this case you're identifying the relationship from the perspective of the BUILDINGS table . Let's now assume that you ask this question:

Does a single building provide space for more than one class?

If the answer is yes, then a one-to-many relationship exists between these tables; otherwise , it's a one-to-one relationship. Once you've identified the relationship, indicate the relationship type in the box located at the junction of the BUILDINGS table row (on the left) and the CLASSES table column (on the top). Figure 10.28 shows the revised table matrix with your entries for the BUILDINGS table.

Figure 10.28. Completed table-matrix entries for the BUILDINGS table.

graphics/10fig28.gif

You've just seen two examples of how to identify a relationship between a distinct pair of tables, so let's take a look at how you identify a self-referencing relationship for a single table. Assume you're working with the STAFF table, and you're now at the junction between the STAFF table on the left and the STAFF table on the top. Using the techniques you learned earlier in this section, you might pose a question such as this:

Can a single staff member be associated with one or more other staff members?

As with the earlier examples, the answer will indicate the type of relationship. Say you received this answer:

Yes, a given staff member can be the spouse of another staff member.

This indicates (rather obviously) that a self-referencing one-to-one relationship exists for the STAFF table. But assume you received this answer instead:

Yes, a single staff member can manage several other staff members.

You probably quickly realized that this answer indicates that a self-referencing one-to-many relationship exists for the STAFF table. Identifying these two types of relationships is a relatively easy task; identifying a self-referencing many-to-many relationship can be slightly more difficult.

This is the type of question you must ask in order to determine whether a table has a self-referencing many-to-many relationship: Can a single ( singular form of the table name ) be associated with one or more other ( plural form of the table name ), and can any of those ( plural form of the table name ) then be associated with yet one or more other ( plural form of the table name )? For example, here's a question you might pose for the STAFF table:

Can a single staff member be associated with one or more other staff members, and can any one of those staff members then be associated with one or more other staff members?

An answer such as the following (or one very similar to it) indicates that the STAFF table has a self-referencing many-to-many relationship:

Yes, a given staff member can manage several other staff members, and any one of those folks can then supervise one or more other staff members.

Once you've identified the type of self-referencing relationship that exists for the table, you indicate it in the table matrix as you would any other relationship.

Relationships will often differ from one perspective to the other, and you must know how to determine what type of relationship officially exists between each pair of tables on the matrix. You make this determination using the following set of formulas; each formula corresponds to a particular relationship type definition. (I've provided the definitions as a point of reference.)

1:1 + 1:1 = 1:1

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.

1:N + 1:1 = 1:N

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.

1:N + 1:N = M:N

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.

Here is the specific procedure you'll use to identify the official relationship between a pair of tables in the matrix. (It incorporates the relationship formulas above.) Let's first look at a generic version of the procedure.

  1. Select a pair of tables and note the entry at the junction between the first table and the second table.

  2. Locate the second table on the same side of the matrix you're working on and note the entry at the junction between it and the first table on the opposite side of the matrix.

  3. Apply the appropriate formula to the two entries and identify the official relationship between the tables.

  4. Diagram the relationship in the appropriate manner.

  5. Cross out both entries on the matrix.

Now, let's take a look at how you apply this procedure to a pair of tables in the matrix. (In this example, you're working down the left-hand side of the matrix.)

  1. Assume you've selected the BUILDINGS and CLASSES tables. You note that the entry at the junction between BUILDINGS and CLASSES is 1:N.

  2. Now you proceed down the left-hand side of the matrix until you locate the CLASSES table and then note that the entry at the junction between the CLASSES and BUILDINGS table is 1:1.

  3. Using these entries with the appropriate formula, you determine that the official relationship between the BUILDINGS and CLASSES tables is 1:N. (1:N + 1:1 = 1:N)

  4. You create a one-to-many relationship diagram for the BUILDINGS and CLASSES tables.

  5. You cross out the entries on the matrix.

Figure 10.29 shows the results of your work.

Figure 10.29. Identifying the official relationship between the BUILDINGS and CLASSES tables.

graphics/10fig29.gif

Note that the relationship diagram is built from the perspective of the BUILDINGS table. This is due to the fact that the BUILDINGS table is on the "one" side of the relationship. When you create a simple diagram such as this, I recommend that you always show the "one" side of the relationship on the left and the "many" side on the right. Following this practice will make your diagrams easy to read and help ensure that you create them in a consistent manner. (This practice is unnecessary, however, when you create a complex diagram showing the relationships between several tables.)

At the very least, you should include each table's primary key in the diagram. Doing so will prove to be a valuable visual aid when you begin to establish the relationships. You could go so far as to display each table's complete structure (as you see in Figure 10.30), assuming you have space on the diagram. Displaying the structures in this manner often helps to reinforce the decision you've made regarding the type of relationship that exists between the tables. (I use both types of diagrams throughout the remainder of the book.)

Figure 10.30. Displaying each table's structure in a relationship diagram.

graphics/10fig30.gif

Note

You'll occasionally find it difficult to identify the exact relationship between a given pair of tables. When this happens, just load the tables with some sample data. This usually helps to reveal the type of relationship that exists between the tables.


It's worth mentioning that this procedure is much easier and shorter when you work with a table that has a self-referencing relationship, such as the STAFF table. As Figure 10.31 illustrates, all you have to do here is diagram the relationship and cross out the entry on the matrix.

Figure 10.31. Working with a self-referencing relationship.

graphics/10fig31.gif

Continue this procedure until you've eliminated all of the entries on the matrix. When you've finished identifying the official relationships among the tables in the database, you can then go through the process of establishing each relationship in the appropriate manner.


   
Top


Database Design for Mere Mortals[c] A Hands-On Guide to Relational Database Design
Database Design for Mere Mortals: A Hands-On Guide to Relational Database Design
ISBN: 0201694719
EAN: 2147483647
Year: 2002
Pages: 203

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net