Two Relationships Between the Same Tables

Let’s consider how we might introduce the idea of teams into the golf club database. We can start off by thinking about what basic information we need to keep about a team. Figure 6-1 shows a class representing a simple team along with some rows in a table that represents the class.

Figure 6-1. The Team class and some rows in a Team table

Now we need to think about relationships between the new Team class and our other classes. The most obvious relationship is that members will play for teams. Figure 6-2 shows a possible class diagram representing this situation.

Figure 6-2. A member can belong to one team

Interpreting the class diagram in Figure 6-2 from left to right, we have that a particular member might play on one team (the 1 nearest the Team class), but a member does not need to play for any teams at all (the 0 nearest the Team class). Reading from right to left, we have that a team could have many members playing for it (the n nearest the Member class) but might not have any (the 0 nearest the Member class). That last statement might seem a bit odd, but when we add new teams, or want to start afresh in a new season, a team might not have any members straight away.

To represent a 1–Many relationship, recall from Chapter 1 that we take the primary key from the table at the 1 end of the relationship and add it as a foreign key to the table at the Many end. Figure 6-3 shows a new foreign key field, Team, which refers to the Team table.

Figure 6-3. Foreign key field Team in the Membertable

Another relationship that is likely to occur between Member and Team is that a member might manage a team. Figure 6-4 shows this additional relationship in the class diagram.

Figure 6-4. Two relationships between the Member and Teamclasses

The top line in Figure 6-4 can be interpreted, from left to right, as stating that a particular member might manage (at most) one team; and from right to left, as that each team has exactly one manager.

This new relationship is a 1–1 relationship. For 1–Many relationships we have always taken the primary key from the one end of the relationship and put it in the table at the other end. This time both ends have a cardinality of 1. We could put a Team_I_Manage column in the Member table or a Manager column in the Team table. The latter is more sensible, as the compulsory Manager attribute is a more important piece of information about teams than the optional Team_I_Manage is for members. Generally, in a 1–1 relationship we take the primary key from the compulsory end (1:1 on the diagram in Figure 6-4) and put that as a foreign key in the other end.

The Team table, with its new Manager foreign key column, is shown along with the Member table in Figure 6-5.

Figure 6-5. Foreign keys Team in Member table and Manager in Team table to represent the relationships in Figure 6-4

From the Member table, we can see that four people play for TeamB (Brenda Nolan, William Cooper, Robert Pollard, and Betty Young), and from the Team table, we can see that member 153 (Brenda Nolan) is the manager of TeamB. You will notice that there is nothing in the data model that says whether or not a manager must be a member of the team. TeamB’s manager is a member of TeamB, whereas TeamA’s manager, 239 (Thomas Spence), is not a member of TeamA. The only constraints implied by the foreign keys are that the manager of a team must be in the Member table and a member can belong only to a team that exists in the Team table.

Some of you may have also realized that making Manager a foreign key does not prevent the same person from managing more than one team. The foreign key constraint does not prevent us from putting member 239 as the manager for both TeamA and TeamB. We have effectively set up a 1–Many relationship between Team and Member for the Manages relationship. If you want to prevent a single member from managing more than one team, you can put a UNIQUE constraint on the Manager column of the Team table. This type of situation is discussed in more depth in my database design book.¹ The following SQL would create a Team table where Manager is a foreign key referring to the Member table and a particular member can only appear once in the Manger column in the table:

CREATE TABLE Team (
TeamName CHAR(10) PRIMARY KEY,
PracticeNight CHAR(20),
Manager INT FOREIGN KEY REFERENCES Member UNIQUE);

Extracting Information from Multiple Relationships

Now that we have the Team and Member tables and their two relationships (Plays for and Manages), we can start extracting information. If we just consider one relationship at a time, it is relatively straightforward to construct queries. If we want a list of the members who play for a team along with the basic information about their teams from the Team table, we can simply join the Member and Team tables on Team = TeamName as in the SQL query here:

SELECT m.MemberID, m.LastName, m.FirstName, m.Team,
       t.TeamName, t.PracticeNight, t.Manager
FROM Member m INNER JOIN Team t ON m.Team = t.TeamName;

A graphical representation and the output of the preceding query is shown in Figure 6-6.

Figure 6-6. Joining Member and Team to get additional information about a member’s team

Similarly, if we want to retrieve information about teams, including the name of the manager, we can join Member and Team on Manager = MemberID:

SELECT t.TeamName, t.PracticeNight, t.Manager,
       m.MemberID, m.LastName, m.FirstName
FROM Team t INNER JOIN Member m ON t.Manager = m.MemberID;

A graphical representation and the output of the preceding query is shown in Figure 6-7.

Figure 6-7. Joining Member and Team to get additional information about a team’s manager

Now we will look at how to retrieve information involving both relationship types.

Process Approach

The information from the join shown in Figure 6-6 is not particularly helpful. We have the managers’ IDs, but it would be more useful to have their names as well. We need another join. First, we’ll have a look at what Access will do by default if you add both the Member and Team tables onto the query design interface. This is shown in Figure 6-8.

Figure 6-8. Default joins in Access if Member and Team are added to diagrammatic query interface

A look at the SQL for the query in Figure 6-8 reveals it is joining the tables like this:

SELECT *
FROM Member m  INNER JOIN Team t
ON t.TeamName = m.Team AND m.MemberID = t.Manager;

Can you figure out what question this query is answering? The output is shown in Figure 6-9.

Figure 6-9. Output for the default Access join in Figure 6-8

To understand what is happening with the preceding join it is useful to consider the Cartesian product of Member and Team. The Cartesian product gives us every combination of rows from each table. The join condition says show only rows where the MemberID is the same as the Manager and where Team and TeamName are the same. In everyday language, this amounts to “Show me the members who manage the team they are in.” For our data, that is just the single row for Brenda Nolan we see in Figure 6-9.

So, how do we construct a query that will show us member names, their teams, and the names of the teams’ managers? The query that follows will provide the information about the members, their teams, and the managers’ IDs (t.Manager); however, it does not provide the managers’ names:

SELECT m.MemberID, m.LastName, m.FirstName, t.TeamName, t.Manager
FROM Member m INNER JOIN Team t ON m.Team = t.TeamName;

What we need to do is to take the result of the preceding join and join that to a second copy of the Member table (m2) to retrieve the names of the managers. We want the join condition to be that t.Manager = m2.MemberID so we get the names of the manager. Figure 6-10 shows a diagrammatic representation and the output of the two joins.

Figure 6-10. Two joins and two copies of Member table to include names of team managers

The first join gives us the member information from the first copy of the Member table and the information from the Team table for that member; the second join gives us the name of the team manager from the second copy of the Member table. The SQL for the two joins is:

SELECT *
FROM (Member m INNER JOIN Team t ON m.Team = t.TeamName)
     INNER JOIN Member m2 ON t.Manager = m2.MemberID;

You might find it instructive to compare this latest query and output with the query involving a single join between the Member and Team tables shown in Figures 6-8 and 6-9.

We are now in position to generate a variety of reports about teams and their members. Figure 6-11 shows a report based on the preceding query and its output, shown in Figure 6-10.

Figure 6-11. A report based on the query shown in Figure 6-10

The report has been grouped by team, with the team and manager information (from the Team table and m2 copy of the Member table) in a group header. The members of the team (from the first copy m of the Member table) are in the detail part of the report.

Outcome Approach

We will now look at an alternative way to construct a query to retrieve all the information about a team (members’ names, team name, and manager’s name) for a report like the one in Figure 6-11. I find the idea of two joins quite intuitive, but other people prefer a different approach.

I have reproduced the two tables in Figure 6-12. Now, without thinking about joins, let’s see how we can pick a member and find out what team he or she is on and who the manager is for that team.

Figure 6-12. Finding a team member (William Cooper), his team’s name, and the name of the team’smanager

Without needing to think about joins, we can find the information we require. We need information from three rows. Let’s look at one specific case. One row (m) from the Member table will give us the name of a member (William Cooper in Figure 6-12). We need to find the row (t) in the Team table for his team (m.Team = t.TeamName). Then we need another row in the Member table (m2) for the manager of the team (t.Manager = m2.MemberID).

With help from Figure 6-12 we can construct the following SQL:

SELECT m.LastName, m.FirstName, m.Team, m2.LastName, m2.FirstName
FROM Member m, Team t, Member m2
WHERE m.Team = t.TeamName AND t.Manager = m2.MemberID

We could replace m.Team with t.TeamName in the SELECT clause of the preceding query if we wish.

The preceding query is equivalent to the query with the two joins. The FROM clause is the Cartesian product of the three tables. The WHERE clause provides the join condition for the join between Member (m) and Team (t) on m.Team = t.TeamName and the join condition for the join between Team and another copy of Member (m2) on t.Manager = m2.MemberID.

Business Rules

The data model from Figure 6-4 is redisplayed below as Figure 6-13.

Figure 6-13. Two relationships between the Member and Team classes

Members can belong to teams, and members can manage teams. When we implement these relationships with foreign keys, the constraints that are placed on the data are quite simple. A member can only be on a team that exists in the Team table, and a team can be managed only by someone in the Member table.

Other constraints are likely to apply in various situations. For example, we might have the additional constraints that a team can have no more than four members or that the manager must be a member of the team (or not). These types of constraints are commonly referred to as business rules. The data model in Figure 6-13 might underpin a database for two different golf clubs. While the basic integrity rules will apply for both clubs (e.g., a member cannot be on a team that doesn’t exist), each club might have different rules about sizes of teams and who can manage them. The foreign key constraints are not sufficient to enforce such business rules.

Relational database products will usually provide some way to enforce business rules. Large systems such as SQL Server and Oracle provide triggers. Triggers are actions that take place when a specified event occurs (for example, when inserting or updating a record). The trigger will reject any changes that do not obey the rules. In Access and other products, it is not possible to apply such constraints to the tables themselves. However, you can attach macros to input forms. These macros will check the data on the form before it is committed to the database. The issue with this approach is that there is no such checking if a user bypasses the form and enters data directly into a table with (for example) an SQL update command.

We won’t look in detail at how business rules are implemented in different products, but we will look at how queries can help find any instances where the constraints are not satisfied. Although this is finding the problem after it has occurred, variations of these queries would form a basis for any trigger or macro that you would need to write to enforce the constraints.

Let’s look at finding teams whose managers are not members of the team. My mind often goes blank when faced with a query like this, and in that case, I always take an outcome approach. This means picturing the tables involved and imagining the type of instance I am seeking. Have a look at Figure 6-14.

Figure 6-14. Finding teams whose managers are not members of the team

In Figure 6-14, we see in the Team table that TeamA’s manager is 239, and we can see in the Member table that member 239 is not a member of any team. If we had a constraint that managers must belong to the team, TeamA would not obey it.

To find all teams like this, we would say:

Find the team names from all the rows (t) in the Team table where the matching row (m) in the Member table for the team manager (i.e., t.Manager = m.MemberID) has a team (m.team) that is either empty or different from the team in the Team table (m.Team <> t.TeamName).

The equivalent SQL is shown here:

SELECT t.teamname
FROM Member m, Team t
WHERE m.MemberID = t.Manager
AND (m.Team <> t.Teamname OR m.Team IS NULL)

The middle two lines are equivalent to a join between the two tables on m.MemberID = t.Manager, and the final line finds those managers who are on a different team or not on a team at all. The following query will produce an equivalent output but uses the inner join notation:

SELECT t.teamname
FROM Member m INNER JOIN Team t ON m.MemberID = t.Manager
WHERE m.Team <> t.Teamname OR m.Team IS NULL

Just a note about why we have included the IS NULL condition in the two queries: You might remember from Chapter 2 that if we make a comparison with a null value, the result is neither true nor false. If we want to find managers who aren’t in a team, we need to specifically include that possibility in our query. Had the requirement been just that a manager must not belong to a different team, we could have left out the checking of null values, because a manager with no team would have been OK. As always, clearly understanding what you are actually trying to find is the most important part of specifying a query.

The two preceding queries will find teams with incorrect managers, but only after they have been added to the database. How do we prevent them from being added in the first place? The solution depends on the database implementation. Before changes to data are finally committed to a database, they are usually recorded in a buffer of some sort. For example, in SQL Server, the records being updated or added are kept in a temporary table called inserted. If we add or update some records to the Team table, a temporary table (inserted) that has the same structure as the Team table is created to hold the new or updated records temporarily. We want to perform a query to check if any new records about to be added to the Team table have managers that don’t obey the constraint. However, instead of looking at the Team table, we want to look at the records in the temporary inserted table and count how many of those are invalid.

The following SQL query, which is very similar to the previous two queries, will count how many of the rows in the inserted buffer for the Team table have managers that do not obey the business rule about managers belonging to the team they manage:

SELECT COUNT(*)
FROM Member m INNER JOIN inserted i ON m.MemberID = i.Manager
WHERE m.Team <> i.Teamname OR m.Team IS NULL

If this count is not zero then there are rows that are about to be inserted that do not obey the rules. In that case we want to rollback the insertion so the rows do not get committed to the Team table. The following SQL statement would be included in a trigger in SQL. The trigger would need to be assigned to run on updating or inserting rows in the Team table.

IF
   (SELECT COUNT(*)
    FROM Member m INNER JOIN inserted i ON m.MemberID = i.Manager
    WHERE m.Team <> i.Teamname OR m.Team IS NULL)
    <> 0)
BEGIN
    Rollback Tran
END

This is a bit of a crude approach, because if any of the new records are incorrect, the whole lot gets rejected. You will need to consult the documentation for your database product to see how to develop triggers that work efficiently, but the idea of using a query to check the validity of new records is a common one.

In Access, the checking is done at the interface level, usually on a form. Instead of checking the inserted table as in the previous query, we would create a macro with a similar query to investigate the values of fields on the form before committing them to the database.

Summary

There can be more than one relationship between tables. For example, “a member may belong to a team” is one relationship. “A team has a club member who is the manager” is another relationship. Finding information about a member’s team (including the manager’s ID) requires a join between Member and Team. If we want to also find the name of the manager, we need to join that result to a second copy of the Member table, like this:

SELECT * FROM
(Member m INNER JOIN Team t ON m.Team = t.TeamName)
INNER JOIN Member m2 ON t.Manager = m2.MemberID

There can be quite complex business rules or constraints involving the relationships between tables. For example, we might require that the manager be a member of the team he or she manages, or that a manager should not be a member of any team, or that a team must have fewer than six members. These often require the use of triggers. The types of queries discussed in this chapter will be helpful in formulating the code required in triggers.