Do link tables need a meaningless primary key fiel

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

Composite PK and turn off clustering.

Answer 2

I have used composite key to prevent duplicate entry and let the database handle the exception. With a single key, you are rely on the front-end application to check the database for duplicate before adding a new record.

Answer 3

I (almost) always use the additional single-column primary key. This generally makes it easier to build user interfaces, because when a user selects that particular linking entity I can identify with a single integer value rather than having to create and then parse compound identifiers.

Answer 4

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.

Answer 5

There is something called identifying and non-identifying relationship. With identifying relationships the FK is a part of the PK in the many-to-many table. For example, say we have tables Person, Company and a many-to-many table Employment. In an identifying relationship both fk PersonID and CompanyID are part of the pk, so we can not repeat PersonID, CompanyID combination.

TABLE Employment(PersonID int (PK,FK), CompanyID int (PK,FK))

Now, suppose we want to capture history of employment, so a person can leave a company, work somewhere else and return to the same company later. The relationship is non-identifying here, combination of PersonID, CompanyID can now repeat, so the table would look something like:

TABLE Employment(EmploymentID int (PK), PersonID int (FK), CompanyID int (FK), 
                     FromDate datetime, ToDate datetime)

Answer 6

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.