Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: DB Design question: Do you create a separate PK in the join table?

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

From: sack <news_at_webpre.com>
Date: 13 Jan 2000 01:19:12 GMT
Message-ID: <85j96g$qu6$0@216.39.144.218>


I am certainly no db guru, so don't base you decision on this, in fact I am more writing to find out more.

My thinking is, if it doesn't have any use, leave it out. i.e. I'd use approach 1)

The primary key (user_id, account_name) will serve as a data integrity constraint, and as an index on user_id, which you probably will need anyway.

following approach 2) you would probably want to also create a unique(user_id, account_name) constraint, and an index on user_id (as well as an index on account_ID in both cases)

So the advantage would be saving an extra index and constraint.

OTOH, you seem to have your use for that artificial PK column, please elaborate on what I am missing out on.
In that case I think the reasoning of your frontend programmers is pretty lame (what's so confusing about inserting the Next_val of a sequence?)

Christian

gmei_at_my-deja.com wrote:
>
> 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 Wed Jan 12 2000 - 19:19:12 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US