Alex Rivera | Logout

Do link tables need a meaningless primary key field?

Asked 2009-12-03T21:57:19.647
39

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?

sql database-design primary-key
Edit
Report

2 Answers

0

The correct answer is:

  • Primary key is ('table1fk' , 'table2fk')
  • Another index on ('table2fk' , 'table1fk')

Because:

  • You don't need an index on table1fk or table2fk alone: the optimiser will use the PK
  • You'll most likely use the table "both" ways
  • Adding a surrogate key is only needed because of braindead ORMs
answered 2009-12-04T05:39:34.627
-2

i've used both, the only benefit of using the first model (with uid) is that you can transport the identifier around as a number, whereas in some cases you would have to do some string concatenation with the composite key to transport it around.

i agree that not indexing the foreign keys is a bad idea whichever way you go.

answered 2009-12-03T22:01:49.037

Your Answer