[Previous] [Contents] [Next]

Step 5: Many-to-many relationships

For many-to-many relationships-there is one in our model between wine and variety-the following procedure is used:

1. Create a new table with a composite name made of the two entities that are related.

2. Add the primary keys of the two related entities to this new table.

3. Add an ID attribute if the order of relationship is important. For example, in the winestore, a Cabernet Merlot Shiraz is different from a Shiraz Merlot Cabernet, so an ID is required.

4. Define the primary key of this new table to be all attributes that form part of the table.

In the example, create the following table:

CREATE TABLE wine_variety (
  wine_id int(5) DEFAULT '0' NOT NULL,
  variety_id int(3) DEFAULT '0' NOT NULL,
  id int(1) DEFAULT '0' NOT NULL
  PRIMARY KEY (wine_id, variety_id)
);

The table contains the primary keys of the wine and grape_variety and defines these-along with the ID attribute-as the PRIMARY KEY. No change is required to the wine or grape_variety tables.

[Previous] [Contents] [Next]