sql - Do link tables need a meaningless primary key field?

Question

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.

I came up with a few possible strictures for the link table:

Traditional 3 column model

• id - auto-numbered PRIMARY
• table1fk - foreign key
• table2fk - foreign key

It's a classic, in most of the books, 'nuff said.

Indexed 3 column model

• id - auto-numbered PRIMARY
• table1fk - foreign key INDEX ('table1fk')
• table2fk - foreign key INDEX ('table2fk')

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.

Composite key 2 columns ADD PRIMARY KEY ('table1fk' , 'table2fk')

• table1fk - foreign key
• table2fk - foreign key

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.

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?

Answer 1

I would use composite key, and no extra meaningless key.

I would not use a ORM system that enforces such rules on my db structure.