Build Your Own DatabaseDriven Website Using PHP amp;amp; MySQL [Electronic resources] نسخه متنی

Many-to-Many Relationships

Okay, you've now got a steadily-growing database of jokes published
on your Website. It's growing so quickly, in fact, that the number of jokes
has become unmanageable! People who visit your site are faced with a mammoth
page that contains hundreds of jokes listed with no structure whatsoever.
Something has to change.

You decide to place your jokes into categories such as "Knock-Knock
Jokes", "Crossing the Road Jokes", "Lawyer Jokes", and "Political Jokes".
Remembering our rule of thumb from earlier, you identify joke categories as
a different type of "thing", and create a new table for them:

mysql>CREATE TABLE Categories (
    ->  ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->  Name VARCHAR(255)
    ->);
Query OK, 0 rows affected (0.00 sec)

Now you come to the daunting task of assigning categories to your jokes.
It occurs to you that a "political" joke might also be a "crossing the road"
joke, and a "knock-knock" joke might also be a "lawyer" joke. A single joke
might belong to many categories, and each category will contain many jokes.
This is a many-to-many relationship.

Once again, many inexperienced developers begin to think of ways to
store several values in a single column, because the obvious solution is to
add a Categories column to the Jokes table and use it to list the ID's
of those categories to which each joke belongs. A second rule of thumb would
be useful here: if you need to store multiple values in a single
column, your design is probably flawed.

The correct way to represent a many-to-many relationship is to use a look-up
table.
This is a table that contains no actual data, but which defines pairs of entries
that are related. "The JokeLookup table associates
pairs of rows from the Jokes and Categories tables" shows what the database design
would look like for our joke categories.

The JokeLookup table associates
pairs of rows from the Jokes and Categories tables

The JokeLookup table associates joke
IDs (JID) with category IDs (CID). In this example, we can see that the joke that
starts with "How many lawyers…" belongs to both the "Lawyer" and "Light
Bulb" categories.

A look-up table is created in much the same way as is any other table.
The difference lies in the choice of the primary key. Every table we've created so
far has had a column named ID that was
designated to be the PRIMARY KEY when the table was created.
Designating a column as a primary key tells MySQL not to allow two entries
to have the same value in that column. It also speeds up join operations based
on that column.

In the case of a look-up table, there is no single column that we want
to force to have unique values. Each joke ID may appear more than once, as
a joke may belong to more than one category, and each category ID may appear
more than once, as a category may contain many jokes. What we don't want to
allow is the same pair of values to appear in the table
twice. And since the sole purpose of this table is to facilitate joins, the
speed benefits offered by a primary key would come in very handy. For this
reason, we usually create look-up tables with a multi-column primary key as
follows:

mysql>CREATE TABLE JokeLookup (
    ->  JID INT NOT NULL,
    ->  CID INT NOT NULL,
    ->  PRIMARY KEY(JID,CID)
    ->);

This creates the table in which the JID and CID columns together form the primary key. This enforces
the uniqueness that is appropriate to a look-up table, preventing a particular
joke from being assigned to a particular category more than once, and speeds
up joins that make use of this table.

With your look-up table in place and containing category assignments,
you can use joins to create several interesting and very practical queries.
This query lists all jokes in the "Knock-Knock" category:

mysql>SELECT JokeText
    ->FROM Jokes, Categories, JokeLookup
    ->WHERE Name="Knock-Knock" AND
    ->  CID=Categories.ID AND JID=Jokes.ID;

The following query lists the categories that contain jokes that begin
with "How many lawyers...":

mysql>SELECT Categories.Name
    ->FROM Jokes, Categories, JokeLookup
    ->WHERE JokeText LIKE "How many lawyers%"
    ->  AND CID=Categories.ID AND JID=Jokes.ID;

And this query, which also makes use of our Authors table
to form a join of four tables (!!!), lists the names of all authors who have
written knock-knock jokes:

mysql>SELECT Authors.Name
    ->FROM Jokes, Authors, Categories, JokeLookup
    ->WHERE Categories.Name="Knock-Knock"
    ->  AND CID=Categories.ID AND JID=Jokes.ID
    ->  AND AID=Authors.ID;