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

Question

Let's say I've got Alpha things that may or may not be or be related to Bravo or Charlie things.

These are one-to-one relationships: No Alpha will relate to more than one Bravo. And no Bravo will relate to more than one Alpha.

I've got a few goals:

• a system that's easy to learn and maintain.
• data integrity enforced within my database.
• a schema that matches the real-world, logical organization of my data.
• classes/objects within my programming that map well to database tables (à la Linq to SQL)
• speedy read and write operations
• effective use of space (few null fields)

I've got three ideas…

PK = primary key  
FK = foreign key  
NU = nullable

One table with many nullalbe fields (flat file)…

      Alphas
      --------
   PK AlphaId
      AlphaOne
      AlphaTwo
      AlphaThree
   NU BravoOne
   NU BravoTwo
   NU BravoThree
   NU CharlieOne
   NU CharlieTwo
   NU CharlieThree

Many tables with zero nullalbe fields…

      Alphas
      --------
   PK AlphaId
      AlphaOne
      AlphaTwo
      AlphaThree

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

      Charlies
      --------
FK PK AlphaId
      CharlieOne
      CharlieTwo
      CharlieThree

Best (or worst) of both: Lots of nullalbe foreign keys to many tables…

      Alphas
      --------
   PK AlphaId
      AlphaOne
      AlphaTwo
      AlphaThree
NU FK BravoId
NU FK CharlieId

      Bravos
      --------
   PK BravoId
      BravoOne
      BravoTwo
      BravoThree

      Charlies
      --------
   PK CharlieId
      CharlieOne
      CharlieTwo
      CharlieThree

What if an Alpha must be either Bravo or Charlie, but not both?

What if instead of just Bravos and Charlies, Alphas could also be any of Deltas, Echos, Foxtrots, or Golfs, etc…?

---

EDIT: This is a portion of the question: Which is the best database schema for my navigation?

Answer 1

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 AlphaId
FK PK AlphaType == "Bravo"
      BravoOne
      BravoTwo
      BravoThree

      Charlies
      --------
FK PK AlphaId
FK PK AlphaType == "Charlie"
      CharlieOne
      CharlieTwo
      CharlieThree

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