1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

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
column
cust_id primary key, link to cust_id of customer details table
lastname
firstname
customer details table
column
cust_id primary key, link to cust_id of customer name table
height
weight
dateofbirth
cust_idlastnamefirstname
0001 henry john
0002 smith adam
0003 johnson mary
0004 bailey harry
0005 morgan alex
cust_idheightweightdateofbirth
0001 181 75 03/05/1960
0002 179 82 06/08/1974
0003 171 65 04/01/1955
0004 185 93 05/05/1980
0005 168 71 09/09/1975
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
column
cust_id primary key
lastname
firstname
height
weight
dateofbirth
cust_idlastnamefirstnameheightweightdateofbirth
0001 henry lohn 181 75 03/05/1960
0002 smith adam 179 82 06/08/1974
0003 johnson mary 171 65 04/01/1955
0004 bailey harry 185 93 05/05/1980
0005 morgan alex 168 71 09/09/1975

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

Share This Page