title: Do link tables need a meaningless primary key field?
body: <p>I am working on a couple of link tables and I got to thinking (Danger Will Robinson, Danger) what are the possible structures of a link table and what are their pro's and con's.</p>
  <p>I came up with a few possible strictures for the link table:</p>
  <h3>Traditional 3 column model</h3>
  <ul>
  <li>id - auto-numbered PRIMARY</li>
  <li>table1fk - foreign key</li>
  <li>table2fk - foreign key</li>
  </ul>
  <p>It's a classic, in most of the books, 'nuff said.</p>
  <h3>Indexed 3 column model</h3>
  <ul>
  <li>id - auto-numbered PRIMARY</li>
  <li>table1fk - foreign key <code>INDEX ('table1fk')</code></li>
  <li>table2fk - foreign key <code>INDEX ('table2fk')</code></li>
  </ul>
  <p>In my own experience, the fields that you are querying against are not indexed in the traditional model. I have found that indexing the foreign key fields does improve performance as would be expected.  Not a major change but a nice optimizing tweak.</p>
  <h3>Composite key 2 columns <code>ADD PRIMARY KEY ('table1fk' , 'table2fk')</code></h3>
  <ul>
  <li>table1fk - foreign key</li>
  <li>table2fk - foreign key</li>
  </ul>
  <p>With this I use a composite key so that a record from table1 can only be linked to a record on table2 once.  Because the key is composite I can add records (1,1), (1,2), (2,2) without any duplication errors.</p>
  <p>Any potential problems with the composite key 2 columns option?  Is there an indexing issue that this might cause?  A performance hit?  Anything that would disqualify this as a possible option?</p>