One-to-one relationship

In a one-to-one relationship, each row in one database table is linked to 1 and only 1 other row in another table. In a one-to-one relationship between Table A and Table B, each row in Table A is linked to another row in Table B. The number of rows in Table A must equal the number of rows in Table B.

To illustrate the one-to-one relationship consider the sample table design and data below:

customer name table
customer details table
Notice that each row in the customer name table is related to 1 and only 1 other row in the customer details table. Similarly, each row in the customer details table is related to 1 and only 1 other row in the customer name table.

However, if you think about it carefully, the above relationship does not really bring any design benefits. In fact, it would cause performance overheads to the database engine for having to link the table rows together to service user queries related to customers. The 2 tables can actually be combined into a single table as illustrated below:

customer table

However, there are some situations in which the one-to-one relationship may improve performance. For our example above, if the height, weight and dateofbirth columns are rarely used, it may make sense to separate them out into a separate database table that is linked to the original table using a one-to-one relationship. This would reduce the overhead needed to retrieve the height, weight and dateofbirth columns whenever a query is performed on the lastname and firstname fields.


See also: One-to-Many relationships and Many-to-Many relationships