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.

Managing Sessions in the Database Tier

In Chapter 8 we discussed the development of session-based applications using the PHP session management features. In this appendix, we:

  • Discuss the motivation for storing session variables in the database tier of a web database application

  • Show how PHP session handlers are written to implement user-defined methods

  • Develop a fully functional set of PHP handlers that use a table in a MySQL database to store session variables