1 <-> 0..1 relationship

[Victor Porton]

I have "1 <-> 0..1 relationship". For example:

There is a set of users. Some users are customers.

Thus we have two tables: the table `users` and the table `customers`.

What is the best way to express this in a relational database:

1. In the table `users` add the `customer` field which may be either a
ref to `users` or NULL.

2. In the table `customers` add UNIQUE `user` field which refers to
`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
don't like this kind of answer. I want a serious piece of DB theory
instead, a well founded answer. Where to read about 1 <-> 0..1
relationships?

[Erwin]

There is no place for NULLs in the relational model of data.

Relational theory has very little answers to "what is the best
database design".

It also has very little to say about that thing that you call
"relationships". (E/R modeling was invented only a couple of years
after the relational model was.)

The logical structure of your data, expressed in relational terms,
seems to be :

VAR RELATION {USERID, <attribute names for user properties here>} USER
KEY {USERID};
VAR RELATION {USERID, <attribute names for customer properties here>}
CUSTOMER KEY {USERID};
CONSTRAINT ALL_CUSTOMERS_ARE_USERS CUSTOMER{USERID} SUBSETOF
USER{USERID};

This covers what you have told us about the problem.