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