Implementing a one to zero or one relationship in SQL

database-designforeign key

Let us say that I am designing a database for a scenario where there exists a one-to-zero-or-one (1-0..1) relationship. For example:

  • There is a set of Users, and some Users may also be Customers.

Thus, I created the two corresponding tables, users and customers, but…

…What is the best way to represent and implement this situation in a given SQL platform? I have considered two possible solutions:

  1. In the users table, add the customer column which may be either a FOREIGN KEY
    reference to customers or a NULL mark.

  2. In the customers table, include a user column (set with a UNIQUE constraint) which points to the users table.

I have already asked a similar question in some forums, but the answer
was basically “whatever you need”, “whatever you think convenient”. I
do not like this kind of answer. I want a serious piece of DB theory
instead, a well founded answer. Where can I read about 1-0..1
relationships?

Best Answer

I want a serious piece of DB theory

Modern relational theory rejects nulls, which would seem to immediately invalidate your option 1. However, this strawman may be eliminated by replacing the default null with a default value e.g. a 'dummy' customer created solely to explicitly model the "is not a customer" correspondence.

I think your option 2 is the most theoretically sound because, unlike the modified option 1, the relations can be in sixth normal form (6NF), being a projection-join normal form and the highest normal form attainable.

I have also heard of a design rule of thumb that states that a relation should model EITHER an entity OR the relationship between entities but never both, which seems sensible to me. Again, this would favour option 2. However, I heard of this rule of thumb many years ago, don't recall where and can offer no serious theoretical basis (other than 6NF as mentioned above).

Related Question