[Previous] [Contents] [Next]

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.

[Previous] [Contents] [Next]