• Choose a category
• All
• Classic-Fiction

By Root 1454 0

on normalization, see Chapter 3 of this minibook.) They just want to get to their data. This table gives them a query that returns the data they’re looking for, even though the underlying data structure has changed dramatically.

Building a view to encapsulate the join

The inner join query is so useful, it’s a dandy place for a view. I created a view from it:

CREATE VIEW heroMissionView AS

SELECT

hero.name AS ‘hero’,

mission.description AS ‘mission’,

mission.villain AS ‘villian’,

mission.plot AS ‘plot’

FROM hero, mission

WHERE

hero.missionID = mission.missionID;

Having a view means that you don’t have to re-create the query each time. You can treat the view as a virtual table for new queries:

SELECT * FROM heroMissionView;

Managing Many-to-Many Joins

Inner joins are a perfect way to implement one-to-many relationships. If you look at ER diagrams, you often see many-to-many relationships, too. Of course, you also need to model them. Here’s the secret: You can’t really do it. It’s true. The relational data model doesn’t really have a good way to do many-to-many joins. Instead, you fake it out. It isn’t hard, but it’s a little bit sneaky.

You use many-to-many joins to handle listed data, such as the relationship between hero and power. Each hero can have any number of powers, and each power can belong to any number of heroes (see the table in Figure 4-14).

The inner join was easy because you just put a foreign key reference to the one side of the relationship in the many table. (See the section “Using an Inner Join to Combine Tables,” earlier in this chapter.) In a many-to-many join, there is no “one” side, so where do you put the reference? Leave it to computer scientists to come up with a sneaky solution.

First, review the hero table in Figure 4-14.

Figure 4-14: The hero table has no reference to powers.

Note that this table contains no reference to powers. Now, look at the power table in Figure 4-15. You see a lot of powers, but no reference to heroes.

Figure 4-15: The power table has no reference to heroes.

Here’s the tricky part. Take a look at a new table in Figure 4-16.

Figure 4-16: This new table contains only foreign keys!

The results of this query may surprise you. The new table contains nothing but foreign keys. It doesn’t make a lot of sense on its own, yet it represents one of the most important ideas in data.

Understanding link tables

The hero_power table shown in Figure 4-16 is a brand new table, and it’s admittedly an odd little duck:

♦ It contains no data of its own. Very little appears inside the table.

♦ It isn’t about an entity. All the tables shown earlier in this chapter are about entities in your data. This one isn’t.

♦ It’s about a relationship. This table is actually about relationships between hero and power. Each entry of this table is a link between hero and power.

♦ It contains two foreign key references. Each record in this table links an entry in the hero table with one in the power table.

♦ It has a many-to-one join with each of the other two tables. This table has a many-to-one relationship with the hero table. Each record of hero_power connects to one record of hero. Likewise, each record of hero_power connects to one record of power.

♦ The two many-to-one joins create a many-to-many join. Here’s the magical part: By creating a table with two many-to-one joins, you create a many-to-many join between the original tables!

♦ This type of structure is called a link table. Link tables are used to create many-to-many relationships between entities.

Using link tables to make many-to-many joins

Figure 4-17 displays a full-blown ER diagram of the hero data.

Figure 4-17: Here’s the ER diagram of the hero data.

Link tables aren’t really useful on their own because they contain no actual data. Generally, you use a link table inside a query or view:

SELECT

hero.name AS ‘hero’,

power.name AS ‘power’

FROM

hero, power, hero_power

WHERE

hero.heroID = hero_power.heroID

AND

power.powerID