Re: 1 <-> 0..1 relationship

On 13 dec, 15:40, Victor Porton <por..._at_narod.ru> wrote:
> 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?

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. Received on Tue Dec 13 2011 - 17:03:57 CET