title: Composite vs Surrogate keys for Referential Integrity in 6NF
body: <p>Take three layers of information:</p>
  
  <h1>Layer 1: Information</h1>
  
  <p>This layer contains data with <code>UNIQUE</code> natural indexes and a surrogate key that is easily transferrable.</p>
  
  <pre><code>Table Surnames:
  
  +-----------------------------+--------------+
  |    ID (Auto Increment, PK)  |    Surname   |
  +-----------------------------+--------------+
  |               1             |     Smith    |
  |               2             |    Edwards   |
  |               3             |     Brown    |
  +-----------------------------+--------------+
  
  Table FirstNames
  
  +-----------------------------+--------------+
  |   ID (Auto Increment, PK)   |   FirstName  |
  +-----------------------------+--------------+
  |               1             |     John     |
  |               2             |     Bob      |
  |               3             |     Mary     |
  |               4             |     Kate     |
  +-----------------------------+--------------+
  </code></pre>
  
  <p><strong>Natural Keys</strong></p>
  
  <p>Alternatively, the two tables above can be without <code>ID</code> and utilize Surname and FirstName as Natural Primary Keys, as explained by Mike Sherrill. In this instance, assume the layer below references <code>varchar</code> rather than <code>int</code>. </p>
  
  <h1>Layer 2: People</h1>
  
  <p>In this layer a composite index is used. This value can be <code>UNIQUE</code> or <code>PRIMARY</code>, depending on whether a surrogate key is utilized as the Primary Key.</p>
  
  <pre><code>+-----------------+--------------+
  |    FirstName    |    LastName  |
  +-----------------+--------------+
  |        1        |       2      |
  |        1        |       3      |
  |        2        |       3      |
  |        3        |       1      |
  |        4        |       2      |
  |       ...       |      ...     |
  +-----------------+--------------+
  </code></pre>
  
  <h1>Layer 3: Parents</h1>
  
  <p>In this layer, relationships between people are explored through a <code>ParentsOf</code> table.</p>
  
  <pre><code>ParentsOf
  
  +-----------------+-----------------+
  |      Person     |   PersonParent  |
  +-----------------+-----------------+
  
   OR
  
  +-----------------+-----------------+-----------------+-----------------+
  | PersonFirstName |  PersonSurname  | ParentFirstName |  ParentSurname  |
  +-----------------+-----------------+-----------------+-----------------+
  </code></pre>
  
  <h1>The Question</h1>
  
  <p>Assuming that referential integrity is VERY important to me at its very core, and I will have <code>FOREIGN KEYS</code> on these indexes so that I keep the database responsible for monitoring its own integrity on this front, and that, if I were to use an ORM, it would be one like <a href="http://docs.doctrine-project.org/en/2.0.x/tutorials/composite-primary-keys.html" rel="noreferrer">Doctrine</a> which has native support for Compound Primary Keys...</p>
  
  <p>Please help me to understand:</p>
  
  <ul>
  <li><p>The list of trade-offs that take place with utilizing surrogate keys vs. natural keys on the 1st Layer.</p></li>
  <li><p>The list of trade-offs that take place with utilizing compound keys vs. surrogate keys on the 2nd Layer which can be transferred over to the 3rd Layer.</p></li>
  </ul>
  
  <p>I am not interested in hearing which is better, because I understand that there are significant disagreements among professionals on this topic and it would be sparking a religious war. Instead, I am asking, very simply and as objectively as is humanly possible, what trade-offs will you be taking by passing surrogate keys to each Layer vs maintaining Primary keys (natural/composite, or surrogate/composite). Anyone will be able to find someone saying <strong><em>NEVER</em></strong> or <strong><em>ALWAYS</em></strong> use surrogate keys on SO and other websites. Instead, a reasoned analyses of trade-offs is what I will most appreciate in your answers.</p>
  
  <p><strong>EDIT:</strong> It has been pointed out that a surname example is a poor example for a use of 6NF. For the sake of keeping the question intact, I am going to leave it be. If you are having trouble imagining the use case for this, a better one might be a list of "Grocery Items". AKA:</p>
  
  <pre><code>+-----------------------------+--------------+
  |   ID (Auto Increment, PK)   |   Grocery    |
  +-----------------------------+--------------+
  |               1             | Sponges      |
  |               2             | Tomato Soup  |
  |               3             | Ice Cream    |
  |               4             | Lemons       |
  |               5             | Strawberries |
  |               6             | Whipped Cream|
  +-----------------------------+--------------+
  
  +-----------------------------+--------------+
  |   ID (Auto Increment, PK)   |   Brand      |
  +-----------------------------+--------------+
  |               1             | Bright       |
  |               2             | Ben &amp; Jerry's|
  |               3             | Store Brand  |
  |               4             | Campbell's   |
  |               5             | Cool Whip    |
  +-----------------------------+--------------+    
  </code></pre>
  
  <p>Natural Composite Key Example:</p>
  
  <pre><code>+-----------------------------+--------------+
  |           Grocery           |   Brand      |
  +-----------------------------+--------------+
  |           Sponges           | Bright       |
  |           Ice Cream         | Ben &amp; Jerry's|
  |           Ice Cream         | Store Brand  |
  |           Tomato Soup       | Campbell's   |
  |           Tomato Soup       | Store Brand  |
  |           Lemons            | Store Brand  |
  |           Whipped Cream     | Cool Whip    |
  +-----------------------------+--------------+ 
  </code></pre>
  
  <p>Recommended Pairings</p>
  
  <pre><code>+-----------------+-----------------+-----------------+-----------------+
  |     Grocery1     |  Brand1        | Grocery2        |  Brand2         |
  +-----------------+-----------------+-----------------+-----------------+
  </code></pre>
  
  <p>To reiterate, this is also just an example. This is not how I would recommend proceeding, but it should help to illustrate my question.</p>
  
  <p>There ARE shortfalls to this method. I'll reiterate that this question was to request walking through the benefits and drawbacks of each method below, not to highlight one as better than another. I believe most people were able to look past the questionable nature of this specific example to answer the core question. This edit is for those that cannot. </p>
  
  <p>There are some very good answers below and if you are curious about which direction to go, please read them.</p>
  
  <p><strong>END EDIT</strong></p>
  
  <p>Thank you!</p>