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;