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

I vote for option 2) -- add an unique value to be primary key, index each of the others.
Also, if the UserAccountTable is used to track changes (add a posting datetime field),
then user_id + account_id would no longer be unique. i.e.,
initially: <u1><a1><a date>
1st change: <u1><a2><a later date>
back to init: <u1><a1><an even later date>

With CRM's being the current fad, one would want to see these changes. (composite key would now need user+account+date -- barf)

I try to avoid composite keys whenever possible. It's my understanding that when given only one field of the composite key, the entire index is scanned.

<gmei_at_my-deja.com> wrote in message news:85irqe$lar$1_at_nnrp1.deja.com...
> 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. The systems I designed and worked before always do that. It just
> makes the DB admin work easier later (in case you might want to do
> batch updates in the join table). But the frond end programmers are
> against this, saying it creates extra complexity, and it will confuse
> them when they do java code.
>
> I am a very open minded person and I am asking you, the experience db
> guru, to give your opinion of what is the "better" option. What are
> the potential problems later in each approach?
>
> The thing I am afraid of is that we made the decision now and 6 months
> later we may need to change it. That will cause lots of problem because
> there are too many places to change. I would rather to make
> the "better" (or "correct") decision now and live with that.
>
> Thanks for your time and you help is appreciated.
>
> If you reply, please send a copy to zlmei_at_hotmail.com
>
> Guang
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Thu Jan 13 2000 - 16:05:13 CST