The one-to-one relationship
In a one-to-one relationship each item of entity A can be associated with 0 or 1 item of entity B. An employee, for example, is usually linked to only 1 office. Or a beer brand has 1 country of origin.
In the same table
The one-to-one relationship is easily modeled: in one table. Table rows contain data that are related in a one-to-one relationship to the primary key or the row. The data for each customer for example, is linked in a one-to-one relationship to its primary key, the customer_id.
In separate tables
In rare cases a one-to-one relationship is modelled using two database tables. Such a setup is sometimes used to overcome the limitations of a database system or to gain performance. Or in rare cases you may decide that you want to separate two entities into different tables, while they are still linked in a one-to-one relationship. Normally having two separate tables in a one-to-one relationship is considered bad practice.
Examples of a one-to-one relationship
- People and their passport. However, this only counts if you look at their current passports. Each person has one current, valid passport and each current, valid passport belongs to one person.
A relational database design is a collection of database tables that are interlinked by primary keys and foreign keys. The relational model comprises a number of rules that help you discover the correct relations between data. These rules are called 'normal forms'. In the following chapters of this database design tutorial I will show you how to normalize a database.