[thelist] SQL join tables

rudy r937 at interlog.com
Fri Jun 29 12:28:29 CDT 2001


> A number of the tables in the database have join tables to eliminate
> many-to-many relationships.

hi lez

well, the many-to-many relationships aren't really "eliminated" -- they are
*implemented* using those join tables, but i know what you meant   ;o)

> The problem is that I have always given those
> join tables their own ID field to provide a primary key
> for the table. The tables in the current database use
> the two foreign keys to provide a "composite primary key".
> Is there any benefit to adding the ID field? I have
> a feeling that the composite key will cause problems.

there are pros and cons to both methods

having a separate primary key means that you can have multiple occurrences
of the composite foreign keys

for example, for tony fernandes and the toronto blue jays, this pair of
keys would be in there four times (whoever heard of a club letting a player
go, and then re-signing him four times!! -- only in toronto, eh)

without a separate pk for the join table, you would then need another
field, date added or something, to make the composite key unique

on the other hand, using the composite key as the pk has the advantage that
it saves a column (and an index on that column)

>Article (ArticleID(PK),  title, body, author)
>Subject (SubjectID(PK), description)
>article_subject(articleID(FK), subjectID(FK))
> - I would add an article_subjectID to this table

rule of thumb #1 -- if it's working, leave it alone   ;o)

in this example, the same article can belong to the same subject only once
(it would seem), so you don't need to add the separate key


rudy





More information about the thelist mailing list