PHP —

Step 3: One-to-one relationships

There is a one-to-one relationship between customer and users in our model. The process for conversion is as follows:

  1. Choose one of the two tables that participates in the relationship (this table has already been identified and written out as part of Steps 1 or 2). If the relationship involves total participation, choose the entity that totally participates.

  2. In the chosen table, include as an attribute (or attributes) the primary key of the other table.

  3. If the entities totally participate in each other and neither participates in another relationship, consider removing one of the tables and merging the attributes into a single table.

As users is the entity that totally participates in customer, the identifier cust_id from customer is added to the users table and defined as the primary key attribute:

CREATE TABLE users (
  cust_id int(4) DEFAULT '0' NOT NULL,
  user_name varchar(50) DEFAULT '' NOT NULL,
  password varchar(15) DEFAULT '' NOT NULL,
  PRIMARY KEY (user_name),
);

Step 4: Regular one-to-many relationships

For a regular one-to-many relationship, here's the procedure:

  1. Identify the table representing the many (M or N) side of the relationship.

  2. Add to the many-side (M or N) table the primary key of the 1-side table.

  3. Optionally, add NOT NULL to any attributes added.

In the model, this means adding a winery_id to the wine table:

CREATE TABLE wine (
  wine_id int(5) DEFAULT '0' NOT NULL auto_increment,
  wine_name varchar(50) DEFAULT '' NOT NULL,
  winery_id int(4),
  type varchar(10) DEFAULT '' NOT NULL,
  year int(4) DEFAULT '0' NOT NULL,
  description blob,
  PRIMARY KEY (wine_id)
);

For the winery table, it means adding a region_id:

CREATE TABLE winery (
  winery_id int(4) DEFAULT '0' NOT NULL auto_increment,
  winery_name varchar(100) DEFAULT '' NOT NULL,
  region_id int(4),
  description blob,
  phone varchar(15),
  fax varchar(15),
  PRIMARY KEY (winery_id)
);

The final regular one-to-many relationship is between wine and item. For this, add a wine_id to items:

CREATE TABLE items (
  cust_id int(5) DEFAULT '0' NOT NULL,
  order_id int(5) DEFAULT '0' NOT NULL,
  item_id int(3) DEFAULT '1' NOT NULL,
  wine_id int(4) DEFAULT '0' NOT NULL,
  qty int(3),
  date timestamp(12),
  price float(5,2),
  PRIMARY KEY (cust_id,order_no,item_id)
);

In cases where you wish to prevent a row being inserted without a corresponding value, you can add a NOT NULL to the attribute added in this step.