Re: 1:1 relationships

João Martiniano wrote:
>
> I recently tried to implement a 1:1 relationship in Microsoft
> Access... but I couldn't. I always thought that 1:1 relationships
> looked like this:
>
> Persons
> --------
> Person ID (primary key)
> Age
> Car ID (foreign key)
>
>
> Cars
> -----
> Car ID (primary key)
> Brand
> Model
> Person ID (foreign key)
>
> My question is: is this the correct way of implementing a 1:1 relationship
> (in Ms Access or any other RDMS) ?

Correct is a relative notion but usually redundancy is a bad idea when you are data modelling. And that is what you have here because you are modeling the same relationship twice; once with a foreign key from Persons to Cars and once with a foreign key from Cars to Persons. And that is also the reason that Access doesn't understand that this is a 1:1 relationship; it thinks that these are simply two independent 1:M relationships. So, you should drop one of the foreign keys. Let us say that you keep the foreign key Car ID in Persons.

The problem is then that Access doesn't know that the remaining foreign key reprents not a 1:M but a 1:1 relationship. As far as I know Access (but I haven't used it much lately) recognizes 1:1 relationships only if the foreign key point to a primary key. It would be nice if you could specify that Car ID in Persons is a (candidate) key. But I doubt if that is possible.

-- 
  Jan Hidders