How do I Justify a one-to-one relationship:

A database developer asked me a couple weeks ago to justify the use of one-to-one relationships within his design document. He stated that the only thing he found on the web concerning one-to-one relationships was ‘Use them when appropriate for your situation’. Now if that is not helpful, I do not know what is. But the web does provide a lot of that advise.

One-to-one relationship: Defined as the existence of exactly one record in Table A that corresponds to one record in Table B.

This type of relationship has to meet one of two criteria before it can be justified to support additional join operations necessary in separation of data across table structures. In the example below we will use Table A as the parent table and Table B the child in a one-to-one relationship.

1st – Does the relationship support the linking of several data attributes.
If Table B contains only a Serial Number and a lookup value there exists no need for the one-to-one relationship. However, if Table B contains a serial number and 6 other attributes or Table B contains a large object(LOB), that may or may not be need in future queries, than a one-to-one relationship is justified. Reasoning: Reduces overall size of Table A on disk, reduces the query speed for searches not including Table B data, and reduces number of blocks to read into memory for Table A.

2nd – How volatile is data in Table B.
Typically, Table A will have 10s or 100s of millions of records and Table B a few dozen to a couple 100 values. Now, if the lookup values in Table B change, it is much quicker to change a couple 100 values than 100s of millions. So you have to know your data and its workings.