What's the best way to handle one-to-one relationships in SQL? [closed]

If you want each Alpha to be related to by only one Bravo I would vote for the possibility with using a combined FK/PK:

      Bravos      --------FK PK AlphaId      BravoOne      BravoTwo      BravoThree

This way one and only one Bravo may refer to your Alphas.

If the Bravos and Charlies have to be mutually exclusive, the simplest method would probably to create a discriminator field:

      Alpha      --------   PK AlphaId   PK AlphaType NOT NULL IN ("Bravo", "Charlie")      AlphaOne      AlphaTwo      AlphaThree      Bravos      --------FK PK AlphaIdFK PK AlphaType == "Bravo"      BravoOne      BravoTwo      BravoThree      Charlies      --------FK PK AlphaIdFK PK AlphaType == "Charlie"      CharlieOne      CharlieTwo      CharlieThree

This way the AlphaType field forces the records to always belong to exactly one subtype.

I'm assuming you will be using SQL Server 2000 / 2005. I have a standard pattern for 1-to-1 relationships which I use, which is not too dissimilar to your 2nd idea, but here are the differences:

• Every entity must have its own primary key first, so your Bravo, Charlie, etc tables should define their own surrogate key, in addition to the foreign key column for the Alpha table. You are making your domain model quite inflexible by specifying that the primary key of one table must be exactly the same as the primary key of another table. The entities therefore become very tightly coupled, and one entity cannot exist without another, which is not a business rule that needs to be enforced within database design.

• Add a foreign key constraint between the AlphaID columns in the Bravo and Charlie tables to the primary key column on the Alpha table. This gives you 1-to-many, and also allows you to specify whether the relationship is mandatory simply by setting the nullability of the FK column (something that isn't possible in your current design).

• Add a unique key constraint to tables Bravo, Charlie, etc on the AlphaID column. This creates a 1-to-1 relationship, with the added benefit that the unique key also acts as an index which can help to speed up queries that retrieve rows based on the foreign key value.

The major benefit of this approach is that change is easier:

• Want 1-to-many back? Drop the relevant unique key, or just change it to a normal index
• Want Bravo to exist independently of Alpha? You've already got the surrogate key, all you do is set the AlphaID FK column to allow NULLs

Personally, I've had lots of success with your second model, using a PK/FK on a single column.

I have never had a situation where all Alphas were required to have a record in a Bravo or Charlie table. I've always dealt with 1 <-> 0..1, never 1 <-> 1.

As for your last question, that's just that many more tables.