Implementing a one to zero or one relationship in SQL

Question

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?

Answer 1

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).

Answer 2

You have been given the correct answer in part. The real answer comes from your data model and how it got normalized. A key is how you get to the relationship:

• The customers table consists of a number of fields considered for the users table which belong to the the customer concept, and are null unless the user is also a customer (sub-type of user). In this case the customers table inherits the primary key from the users table. (It is possible to multiple sub-types which may or may not overlap.)

• The customers table consists of a number of fields related to the customer concept, but not necessarily the user concept. The customer is a strong table and is not dependent on the user concept. (Removing the users table would not significantly impact the design of the customers table.) In this case the customers table gets its own primary key.

What you are have is a special case of optional one to many relationship where the upper bound is 1. Consider it from both sides: Would it be possible for one user to have multiple customers, or one customer to have multiple users? If so, you will need to remodel your data.

Adding the user-id foreign key to the customers table could be considered a better choice as correctly maps the one to many (upper limit 1) relationship and avoids a nullable field. To enforce the upper limit the foreign key index needs to be unique. This will occur automatically if the primary key is the user-id.

Adding the customer-id as an optional foreign key to the users table enforces the upper limit of 1 in the relationship but reverses the dependency.

Answer 3

Have you considered a little more complex but flexible approach. The parent table is "person" (or "entity" depending on how complex you want to be). Then the customer table and user table each have a FK to the person table. The person table contains personal details while the customer and user tables contain only attributes associated with a user or customer. Often addresses (email and snail mail), phone numbers, etc. are also represented in separate tables with mapping tables to permit many-to-many situations. This is a relatively common model which you can find on a number of reference sites.