Building a Link Table for Many-Many Relationships

Once you've created an ER diagram, you can create new tables to handle all the one-to-many relationships. It's a little less obvious what to do with many-to-many relationships such as the link between agents and skills. Recall that each agent can have many skills, and several agents can use each skill. The best way to handle this kind of situation is to build a special kind of table.

Enhancing the ER Diagram

Figure 9.5 shows a new version of the ER diagram that eliminates all many-many relationships.

Figure 9.5: This newer ER diagram includes a special table to handle the many-many relationship

The ER diagram featured in Figure 9.5 improves on the earlier version in a number of ways. First, I added (PK) to the end of every primary key. I also added (FK) to the end of every foreign key. The placements of the lines in the diagram are now much more important. I now draw a line only between a foreign key reference and the corresponding primary key in the other table. Every relationship should go between a foreign key reference in one table and a primary key in the other. The other main improvement is the addition of the agent_specialty table. This table is interesting because it contains nothing but primary and foreign keys. Each entry in this table represents one link between the agent and specialty tables.

TRICK

Most tables in a relational database are about entities in the data set, but link tables are about relationships between entities.

All the actual data referring to the agent or specialty are encoded in other tables. This arrangement provides a great deal of flexibility.

Creating the specialty Table

The specialty table is actually extremely simple, as shown in Table 9.7.

Table 9.7: THE SPECIALTY TABLE
specialtyID	name
0	Electronics
1	Counterintelligence
2	Sabotage
3	Doily Design
4	Explosives
5	Flower Arranging

As you can see, there is nothing in the specialty table that connects it directly with any particular agent. Likewise, you'll find no references to specialties in the agent table. The complex relationship between these two tables is handled by the new agent_specialty table. This special kind of table is called a link table because it is used to manage the relationships between other tables. Table 9.8 shows a sample set of data in the agent_specialty table.

Table 9.8: THE AGENT_SPECIALTY TABLE
agent_specialty_ID	agentID	specialtyID
1	1	2
2	1	3
3	2	1
4	2	6
5	3	2
6	4	4
7	4	5

Interpreting the agent_specialty Table with a Query

Of course, the agent_specialty table is not directly useful to the user, because it contains nothing but foreign key references. You can translate the data to something more meaningful with an SQL statement:

SELECT agent_specialtyID,
       agent.name AS 'agent',
       specialty.name AS 'specialty'
FROM agent_specialty,
     agent,
     specialty
WHERE agent.agentID = agent_specialty.agentID
  AND specialty.specialtyID = agent_specialty.specialtyID;

It requires two comparisons to join the three tables. It is necessary to forge the relationship between agent and agent_specialty by common agentID values. It's also necessary to secure the bond between specialty and agent_specialty by comparing the specialtyID fields. The results of such a query show that the correct relationships have indeed been joined, as you can see in Table 9.9.

Table 9.9: QUERY INTERPRETATION OF AGENT_SPECIALTY TABLE
agent_specialtyID	agent	specialty
1	Bond	Sabotage
2	Bond	Doily Design
3	Falcon	Counterintelligence
5	Cardinal	Sabotage
6	Blackford	Explosives
7	Blackford	Flower Arranging

The link table provides the linkage between tables that have many-many relationships. Each time you want a new relationship between an agent and a specialty, you add a new record to the agent_specialty table.

Creating Queries That Use Link Tables

Whenever you want to know about the relationships between agents and specialties, the data is available in the agent_specialty table. For example, if you need to know which agents know flower arranging, you can use the following query:

SELECT
  agent.name
FROM
  agent,
  specialty,
  agent_specialty
WHERE agent.agentID = agent_specialty.agentID
  AND agent_specialty.specialtyID = specialtiy.specialtyID
  AND specialty.name = 'Flower Arranging';

This query looks a little scary, but it really isn't as bad as it looks. This query requires data from three different tables. The output will need the name from the agent table. I don't want to remember what specialty number is associated with "Flower Arranging," so I'll let the query look that up from the specialty table. Since I need to know which agent is associated with a particular specialty, I'll use the agent_specialty table to link up the other two tables. The WHERE clause simply provides the joins. The phrase

agents.agentID = agent_specialty.agentID

cements the relationship between agents and agent_specialty. Likewise,

agent_specialty.specialtyID = specialties.specialtyID

ensures the connection between specialties and agent_specialty. The last part of the WHERE clause is the actual conditional part of the query that only returns records where the specialty is flower arranging. (You know, flower arrangement can be a deadly art in the hands of a skilled practitioner...)