title: What's the best way to handle one-to-one relationships in SQL?
body: <p>Let's say I've got Alpha things that may or may not <em>be</em> or be <em>related to</em> Bravo or Charlie things.</p>
  
  <p>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.</p>
  
  <p>I've got a few goals:</p>
  
  <ul>
  <li>a system that's easy to learn and
  maintain.</li>
  <li>data integrity enforced within my
  database.</li>
  <li>a schema that matches the
  real-world, logical organization of
  my data.</li>
  <li>classes/objects within my
  programming that map well to
  database tables (à la Linq to SQL)</li>
  <li>speedy read and write operations</li>
  <li>effective use of space (few null fields)</li>
  </ul>
  
  <p>I've got three ideas&hellip;</p>
  
  <pre><code>PK = primary key  
  FK = foreign key  
  NU = nullable
  </code></pre>
  
  <p>One table with many nullalbe fields (flat file)&hellip;</p>
  
  <pre><code>      Alphas
        --------
     PK AlphaId
        AlphaOne
        AlphaTwo
        AlphaThree
     NU BravoOne
     NU BravoTwo
     NU BravoThree
     NU CharlieOne
     NU CharlieTwo
     NU CharlieThree
  </code></pre>
  
  <p>Many tables with zero nullalbe fields&hellip;</p>
  
  <pre><code>      Alphas
        --------
     PK AlphaId
        AlphaOne
        AlphaTwo
        AlphaThree
  
        Bravos
        --------
  FK PK AlphaId
        BravoOne
        BravoTwo
        BravoThree
  
        Charlies
        --------
  FK PK AlphaId
        CharlieOne
        CharlieTwo
        CharlieThree
  </code></pre>
  
  <p>Best (or worst) of both: Lots of nullalbe foreign keys to many tables&hellip;</p>
  
  <pre><code>      Alphas
        --------
     PK AlphaId
        AlphaOne
        AlphaTwo
        AlphaThree
  NU FK BravoId
  NU FK CharlieId
  
        Bravos
        --------
     PK BravoId
        BravoOne
        BravoTwo
        BravoThree
  
        Charlies
        --------
     PK CharlieId
        CharlieOne
        CharlieTwo
        CharlieThree
  </code></pre>
  
  <p>What if an Alpha must be either Bravo or Charlie, but not both?</p>
  
  <p>What if instead of just Bravos and Charlies, Alphas could also be any of Deltas, Echos, Foxtrots, or Golfs, etc&hellip;?</p>
  
  <hr>
  
  <p><strong>EDIT:</strong> This is a portion of the question: <a href="https://stackoverflow.com/questions/56981/which-is-the-best-database-schema-for-my-navigation#57056">Which is the best database schema for my navigation?</a></p>