One (optional) to Many
Diagram 1o2M.png
How to read For any A there are many Bs. For any B there is no more than one A. (Equivalently, there may be one A).
Relevant relations The many side of this relation can be either optional or mandatory.

Consider the alternatives of embedding a column from one table in the other table: embedding b_id in the A table or the a_id in the B table. Suppose we put the b_ids in A. By embedding the b_id in the A table, we are associating one and only one B entity with a particular A entity. This cannot be the situation according to the ER diagram, because for any A entity there are possibly many B entities—-so you would possibly have to put many b_ids in one record. By rule #1 we can't do this.

Suppose we put the a_ids in B. By embedding the a_id in the B table, we are associating one and only one A entity with a particular B entity. By the ER diagram, for any B entity there are zero or more A entities. If there are many B entities for which there are not associated A entities, then many rows of the B table would have nulls in the a_id column. Rule #2 tells us this is not adviseable. (If there are only a few rows that would have nulls, then this choice still might be the preferable one. It is a matter of knowing the data that tells you which you should choose.)

Another choice is to create a third table: the R relation with a_id and b_id embedded in it. First we need to determine what the key should be. The obvious choices are to use either a_id alone or b_id alone. Again, for every A entity there are potentially many B entities. This means that a_id might appear in many rows of R; therefore, a_id cannot be the key by itself. Consider b_id: For every B entity there are either zero or one A entities. If there are zero A entities, then b_id will not appear in any rows in R. This is not objectionable. If there is one A entity associated with the B entity, then b_id will appear in one row (with the appropriate a_id in the other column in that row). Thus, b_id can serve as the primary key of the R table.

Given the above discussion, the table structure is as follows (see this page for how this should be interpreted):

A(__a_id__, a_name, <a_other>)
B(__b_id__, b_name, <b_other>)
R(__fk_r_b*__, fk_r_a*, <r_other>)

Given this table structure, we must only determine which foreign keys can be null and which are unique. There are two foreign keys in these tables. Every row in the R table represents a relationship between an entity in the A table and an entity in the B table. If either column is null, it should not be a row in the R table since this would mean that there is not a relationship between the two entities. The column fk_r_a cannot be unique since each A entity can possibly be in many relationships. The column fk_r_b is unique since an B entity can be related to at most one A entity. Since it is unique and cannot be null, we can use this column as the primary key for the R table. Given this information, we have the following SQL create statements (see this page for how this should be interpreted):

create table A
  (a_id type,
   a_name type,
   primary key (a_id));
create table B
  (b_id type,
   b_name type,
   primary key (b_id));
create table R
  (fk_r_b type,
   fk_r_a type not null,
   primary key (fk_r_b),
   foreign key (fk_r_a) references A,
   foreign key (fk_r_b) references B);

Back to ER to SQL page.

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-NonCommercial-ShareAlike 3.0 License