Re: DB Design question: Do you create a separate PK in the join table?

In article <85iru9$lc4$1_at_nnrp1.deja.com>, gmei_at_my-deja.com writes
>Hi:
>
>We are designing a new oracle database for shop. We just had a long,
>heated debate about whether we should create an independent Primary key
>in join tables. Fox example:
>We have three tables: UserTable, AccountTable, UserAccountTable. And we
>want to support that a user can have several account and an account can
>be owned by several users (join account).
>
>UserTable:
>User_ID (PK)
>User_FirstName
>User_LastName
>....
>
>AccountTable:
>Account_ID (PK)
>Account_Name
>...
>
>As far as UserAccountTable goes, there are two options:
>
>1. UserAccountTable:
>User_ID (FK)
>Account_ID (FK)
>
>The primary key in this table is the composite key which is made of
>both User_ID and Account_ID.
>
>2. UserAccountTable:
>UserAccountID (PK)
>User_ID (FK)
>Account_ID (FK)
>
>Here you still have FK constraints, but you add one independent PK.
>
>I am for option 2. I think every table should have it's own independent
>PK.

If option the User_ID and the Account_ID uniquely identify a record in the join table then you already have a perfectly good Primary Key. Adding an automatically generated number just creates a new field that is completely dependent on the existing fields, you have denormalised the table. You also have to keep two separate indexes because you still have to check that the two ID fields create a unique identifier.

There are often reasons why you might do that, but it's not something you should do without having a good reason.

You might find that the PK is a long text field which makes it inconvenient to use as an FK in other tables, so you substitute a simple integer. That's fine.

But in this case it's only a link table and the chances are that both of its fields are simple integers anyway and its key will never exist as an FK in another table. Under those circumstances adding another index and 50% to the length of the record is not a good idea.

But of course every system is different. Any of us can offer advice, but we don't have to live with the consequences.

-- 
Bernard Peek
bap_at_shrdlu.com
bap_at_shrdlu.co.uk