• Choose a category
• All
• Classic-Fiction

By Root 1403 0

see only the ones where the hero table’s missionID matches up to the missionID field in the mission table. In other words, you want a query that says only return rows where the two values of missionID are the same. That query may look like Figure 4-12. It’s almost identical to the last query, except this time, a WHERE clause indicates that the foreign key and primary key should match up.

Figure 4-11: This query joins both tables, but it doesn’t seem right.

Figure 4-12: Now, you have an inner join.

This particular setup (using a foreign key reference to join up two tables) is called an inner join. Sometimes, you see the syntax like

SELECT

hero.name AS ‘hero’,

hero.missionID AS ‘heroMID’,

mission.missionID AS ‘missMID’,

mission.description as ‘mission’

FROM

hero INNER JOIN mission

ON

hero.missionID = mission.missionID;

Some of Microsoft’s database offerings prefer this syntax, but it really does the same thing: join up two tables.

Enforcing one-to-many relationships

Whenever your ER diagram indicates a many-to-one (or one-to-many) relationship, you generally use an inner join (see the preceding section). Here’s how you do it:

1. Start with the ER diagram.

No way are you going to get this right in your head! Make a diagram. Use a tool like MySQL Workbench, some other software, pencil and paper, lipstick on a mirror, whatever. You need a sketch.

2. Identify one-to-many relationships.

You may have to talk with people who use the data to determine which relationships are one-to-many. In the hero data, a hero can have only one mission, but each mission can have many heroes. Thus, the hero is the many side, and the mission is the one side.

3. Find the primary key of the one table and the many table.

Every table should have a primary key. (You’ll sometimes see advanced alternatives like multifield keys, but wait until you’re a bit more advanced for that stuff.)

4. Make a foreign key reference to the one table in the many table.

Add a field to the table on the many side of the relationship that contains only the key to the table on the one side.

You don’t need a foreign key in the table on the one side of the relationship. This concept confuses most beginners. You don’t need (or want) a link back to the many table because you don’t know how many links you’ll need. Multiple links would be a listed field, which is exactly what you’re trying to avoid.

If the preceding steps are hard for you to understand, think back to the hero example. Each hero (according to the business rules) can be on only one mission. Thus, it makes sense to put a link to the mission in the hero table because you have only one mission. Each mission can be related to many heroes, so if you try to link missions to heroes, you have listed fields in the mission table, violating the first normal form. (For information on the types of normal forms, see Chapter 3 of this minibook.) Figure 4-13 shows how it works in action. The result of this join looks a lot like the original intention of the database, but now it’s normalized.

Figure 4-13: Here’s a nice join of the hero and mission tables.

Counting the advantages of inner joins

Even though the table in Figure 4-13 contains everything in the original non-normalized data set (except for the repeated field — that’s coming up soon), the new version is considerably better for several reasons:

♦ No data is repeated. The plot is stored only one time in the database. Even though it may appear several times in this output, each value is stored only once.

♦ Searching is much more efficient. Because the data is stored only one time, you no longer have to worry about spelling and typing errors. If the entry is wrong, it is universally wrong, and you can repair it in only one place.

♦ The data is organized correctly. Although the user can’t see it from this output, the tables are now separated so that each type of data goes where it belongs.

♦ The output still looks like what the user wants. Users don’t care about the third normal form. (For more