<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>

[{'answer_id': 57164, 'author': 'Gulzar Nazim', 'author_id': 4337, 'author_profile': 'https://Stackoverflow.com/users/4337', 'pm_score': 1, 'selected': False, 'text': '<p>One more approach is having 3 tables for storing the 3 entities and having a separate table for storing the relations.</p>\n'}, {'answer_id': 57167, 'author': 'Jarrett Meyer', 'author_id': 5834, 'author_profile': 'https://Stackoverflow.com/users/5834', 'pm_score': 2, 'selected': False, 'text': "<p>Personally, I've had lots of success with your second model, using a PK/FK on a single column.</p>\n\n<p>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 &lt;-> 0..1, never 1 &lt;-> 1.</p>\n\n<p>As for your last question, that's just that many more tables.</p>\n"}, {'answer_id': 57169, 'author': 'Craig', 'author_id': 2894, 'author_profile': 'https://Stackoverflow.com/users/2894', 'pm_score': 1, 'selected': False, 'text': '<p>You could have a join table that specifies an Alpha and a related ID.  You can then add another column specifing if it is an ID for Bravo, Charlie or whatever.  Keeps the column creep down on Alpha but does add some complexity to joining queries.</p>\n'}, {'answer_id': 57184, 'author': 'Hafthor', 'author_id': 4489, 'author_profile': 'https://Stackoverflow.com/users/4489', 'pm_score': 0, 'selected': False, 'text': "<p>I'd go with option 1 unless I had a significant reason not to. It might not cost you as much space as you think, esp. if you are using varchars in Bravo. Don't forget that splitting it will cost you for foreign keys, secondary identity and needed indexes.\n<p>A place where you might run into trouble is if Bravo is unlikely to be needed (&lt;%10) AND you need to quickly query by one of its fields so you index it.</p>\n"}, {'answer_id': 57209, 'author': 'David Schmitt', 'author_id': 4918, 'author_profile': 'https://Stackoverflow.com/users/4918', 'pm_score': 3, 'selected': False, 'text': '<p>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:</p>\n\n<pre><code>      Bravos\n      --------\nFK PK AlphaId\n      BravoOne\n      BravoTwo\n      BravoThree\n</code></pre>\n\n<p>This way one and only one Bravo may refer to your Alphas.</p>\n\n<p>If the Bravos and Charlies have to be mutually exclusive, the simplest method would probably to create a discriminator field:</p>\n\n<pre><code>      Alpha\n      --------\n   PK AlphaId\n   PK AlphaType NOT NULL IN ("Bravo", "Charlie")\n      AlphaOne\n      AlphaTwo\n      AlphaThree\n\n      Bravos\n      --------\nFK PK AlphaId\nFK PK AlphaType == "Bravo"\n      BravoOne\n      BravoTwo\n      BravoThree\n\n      Charlies\n      --------\nFK PK AlphaId\nFK PK AlphaType == "Charlie"\n      CharlieOne\n      CharlieTwo\n      CharlieThree\n</code></pre>\n\n<p>This way the AlphaType field forces the records to always belong to exactly one subtype.</p>\n'}, {'answer_id': 57772, 'author': 'Fernando Barrocal', 'author_id': 2274, 'author_profile': 'https://Stackoverflow.com/users/2274', 'pm_score': 1, 'selected': False, 'text': "<p>I have an example working pretty well so far that fits your model:</p>\n\n<p>I Have Charlie and Bravo Tables Having the Foreign Key alpha_id from Alpha. Like your first example, except alpha is not the Primary Key, bravo_id and charlie_id are.</p>\n\n<p>I use alpha_id on every table I need to address to those entities, so, to avoid a SQL that may cause some delay researching both Bravo and Charlie to find which one Alpha is, I created a AlphaType table and on Alpha table I have its id (alpha_type_id) as foreign key. That way I can know in a programmatic way which AlphaType I am dealing with without Joining tables that may have zillions of records. in tSQL:</p>\n\n<pre><code>// For example sake lets think Id as a CHAR.\n// and pardon me on any mistake, I dont have the exact code here,\n// but you can get the idea\n\nSELECT \n  (CASE alpha_type_id\n    WHEN 'B' THEN '[Bravo].[Name]'\n    WHEN 'C' THEN '[Charlie].[Name]'\n    ELSE Null\n  END)\nFROM ...\n</code></pre>\n"}, {'answer_id': 86025, 'author': 'Mike McAllister', 'author_id': 16247, 'author_profile': 'https://Stackoverflow.com/users/16247', 'pm_score': 0, 'selected': False, 'text': "<p>I would create a supertype / subtype relationship.</p>\n\n<pre><code>   THINGS\n   ------\nPK ThingId  \n\n   ALPHAS\n   ------\nFK ThingId (not null, identifying, exported from THINGS)\n   AlphaCol1\n   AlphaCol2\n   AlphaCol3  \n\n   BRAVOS\n   ------\nFK ThingId (not null, identifying, exported from THINGS)\n   BravoCol1\n   BravoCol2\n   BravoCol3  \n\n   CHARLIES\n   --------\nFK ThingId (not null, identifying, exported from THINGS)\n   CharlieCol1\n   CharlieCol2\n   CharlieCol3\n</code></pre>\n\n<p>So, for example, an alpha that has a charlie but not a bravo:-</p>\n\n<pre><code>insert into things values (1);\ninsert into alphas values (1,'alpha col 1',5,'blue');\ninsert into charlies values (1,'charlie col 1',17,'Y');\n</code></pre>\n\n<p>Note, you can't create more than one charlie for the alpha, as if you tried to create a two charlies with a ThingId of 1 the second insert would get a unique index/constraint violation.</p>\n"}, {'answer_id': 86467, 'author': 'devinmoore', 'author_id': 15950, 'author_profile': 'https://Stackoverflow.com/users/15950', 'pm_score': 1, 'selected': False, 'text': '<p>You raise a lot of questions that make it hard to select any of your proposed solutions without a lot more clarification on the exact problem you are trying to solve.  Consider not just my clarification questions, but the criteria that you will use to evaluate my questions, as an indication of the amount of detail required to solve your problem:</p>\n\n<ul>\n<li>a system that\'s easy to learn and maintain. </li>\n</ul>\n\n<p>What "System" will it be easy to learn and maintain?  The source code of your app, or the app\'s data via it\'s end-user interface?  </p>\n\n<ul>\n<li>data integrity enforced within my database.</li>\n</ul>\n\n<p>What do you mean by "enforced within my database"?  Does this mean you cannot by any means control data integrity any other way, i.e. the project requires only DB-based data integrity rules?</p>\n\n<ul>\n<li>a schema that matches the real-world, logical organization of my data.</li>\n</ul>\n\n<p>Can you provide us the real world, logical organization to which you are referring?  It\'s impossible to infer it from your three examples of the data you are trying to store -- i.e. suppose all three of your structures are completely wrong.  How would we know that unless we know the real-world spec?</p>\n\n<ul>\n<li>classes/objects within my programming that map well to database tables (à la Linq to SQL)</li>\n</ul>\n\n<p>This requirement sounds like your hand is being forced to create this with linq to SQL, is that the case?</p>\n\n<ul>\n<li>speedy read and write operations</li>\n</ul>\n\n<p>What is "speedy"?  .03 seconds?  3 seconds?  30 minutes?  It\'s unclear because you\'re not specifying the data size and type of operations to which you are referring.</p>\n\n<ul>\n<li>effective use of space (few null fields)</li>\n</ul>\n\n<p>Effective use of space has nothing to do with the number of null fields.  If you mean a normalized database structure, that will depend again on the real-world spec\'s and other design elements of the application that have not been provided in the question.  </p>\n'}, {'answer_id': 113304, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 2, 'selected': False, 'text': "<p>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:</p>\n\n<ul>\n<li><p>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.</p></li>\n<li><p>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).</p></li>\n<li><p>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.</p></li>\n</ul>\n\n<p>The major benefit of this approach is that change is easier:</p>\n\n<ul>\n<li>Want 1-to-many back? Drop the relevant unique key, or just change it to a normal index</li>\n<li>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</li>\n</ul>\n"}]
['https://Stackoverflow.com/questions/57152', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/83/']