Figure 3.3 Key fields in a one-to-one relationship.
For this reason, most database designers name this field the ID field. For example, the Book table
would have a Book_ID field, and the Transaction table would have a Trans_ID.
To establish your one-to-one relationship, you must designate one of the tables as the primary table and
the other as the secondary table. This is generally an arbitrary decision in a one-to-one relationship. To
make it easy, choose the table that will be affected first when you add a new record to the database.
This primary table will contain a key field. In the example, the Order table will have an Order_ID field
that is unique to this table. The secondary table will have its own unique key field, as well as the key
field from the table with which it shares a relationship. Both of the fields will be unique within the
secondary table. This will create the one-to-one relationship. Figure 3.4 demonstrates this concept.
Figure 3.4 A one-to-one relationship in a database.
One-to-Many
A one-to-many relationship occurs when a record in table one may have many corresponding records in
table 2, and table two has many records that correspond to only one record in table one (see Figure 3.5). At
the bookstore, a one-to-many relationship exists between the Customer table and the Order table. One
customer can have many orders, but the orders only point back to one customer. Figure 3.6 illustrates this
point.
Figure 3.5 A one-to-many relationship.