|How to read||For any A there may be one B. For any B there may be one A.|
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 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.)
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. By the ER diagram, for any A entity there are zero or more B entities. If there are many A entities for which there are not associated B entities, then many rows of the A table would have nulls in the b_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. For every a_id there are zero or one associated b_ids. This means that a_id might appear in zero or one rows of R; therefore, a_id can serve as a unique identifier for a row in R. The same can be said for b_id. Either column can serve as the primary key of R.
Given the above discussion, the table structure is as follows:
A(__a_id__, a_name, <a_other>) B(__b_id__, b_name, <b_other>) R(__fk_r_a__*, fk_r_b*, <r_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 is unique since an A entity can be related to at most one B entity. The column fk_r_b is unique since a B entity can be related to at most one A entity. Given this information, we have the following SQL create statements:
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_a type, fk_r_b type not null, <r_other>, primary key (fk_r_a), foreign key (fk_r_a) references A, foreign key (fk_r_b) references B, unique (fk_r_b));
Back to ER to SQL page.