-
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.
-
In the chosen table, include as an attribute (or attributes) the primary key of the other table.
-
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:
-
Identify the table representing the many (
M
orN
) side of the relationship. -
Add to the many-side (
M
orN
) table the primary key of the1
-side table. -
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.