As an HTML5 and CSS3 programmer, you can use inner joins to combine SQL tables. When you normalize the databases, it often makes sense to break it up into several tables. Take a quick look at the hero table.
You probably noticed that most of the mission information is now gone from this table, except one important field. The missionID field is an integer field that contains the primary key of the mission table. A foreign key is a field that contains the primary key of another table. Foreign keys are used to reconnect tables that have been broken apart by normalization.
Look at the relationship between the mission and hero tables begins to make sense.
The mission table doesn't have a link back to the hero. It can't, because any mission can be connected to any number of heroes, and you can't have a listed field.
How to build a Cartesian join and an inner join
Compare the hero and mission tables, and you see how they fit together. The missionID field in the hero table identifies which mission the hero is on. None of the actual mission data is in the hero field, just a link to which mission the player is on.
Creating a query with both tables is tempting. This query appears to join the tables, but it obviously isn't doing the right thing. You have only three heroes and two missions, yet this query returns six rows! What's happened here is called a Cartesian join. It's a combination of all the possible values of hero and mission, which is obviously not what you want.
You don't really want all these values to appear; you want to 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.
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.
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. Here's how you do it:
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.
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.
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.)
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. The result of this join looks a lot like the original intention of the database, but now it’s normalized.
Many programmers get confused with this example, saying heroes should be allowed to go on multiple missions, or they're not very good heroes. That's a great point, and it brings up one of the most significant issues in data development. The data programmer's job is to reflect the business rules in place.
The business rules in this example were deliberately made up to simplify explaining things, so you’ve got a business rule in place (one mission per hero) that may not be the best from a "saving the world" perspective. However, if that's the business rule you've got, your job is to implement it.
How to build a view to encapsulate the join
The inner join query is so useful, it's a dandy place for a view. A view an be created from it:
CREATE VIEW heroMissionView AS SELECT hero.name AS 'hero', mission.description AS 'mission', mission.villain AS 'villain', 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;