One (optional) to Many
Diagram For any A there are many Bs. For any B there is no more than one A. (Equivalently, there may be one A). 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,
<a_other>,
primary key (a_id));
create table B
(b_id type,
b_name type,
<b_other>,
primary key (b_id));
create table R
(fk_r_b type,
fk_r_a type not null,
<r_other>,
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.

page revision: 3, last edited: 20 Aug 2008 21:20
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-NonCommercial-ShareAlike 3.0 License