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 t able?

RE: DB Design question: Do you create a separate PK in the join t able?

From: Berger, Gene <BERGERGENE_at_PIOS.com>
Date: Mon, 17 Jan 2000 18:33:41 -0500
Message-ID: <07E164D341EED111ADCD0000F8662F4A086CA743@pioushqntmail2.pios.com>


I may not know Oracle that well but I've been doing databases for 17 years. Definitely #2. A table that has a unique primary key that has nothing to do with anything specific is in the long run, the best thing. In the long run because now two fields that SEEM to be unique could become nonUNIQUE in the future. I've seen it happen time and time again. People swear (sales mgrs, etc.) that those fields will never repeat but eventually most do. (A single field primary key is also faster during joins.)

-----Original Message-----
From: Steve McDaniels [mailto:steve.mcdaniels_at_sierra.com] Sent: Thursday, January 13, 2000 5:05 PM To: comp.databases.oracle.misc_at_list.deja.com Subject: Re: DB Design question: Do you create a separate PK in the join table?

 Message from the Deja.com forum:
 comp.databases.oracle.misc
 Your subscription is set to individual email delivery

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.



 Deja.com: Before you buy.
 http://www.deja.com/

 Sent via Deja.com http://www.deja.com/  Before you buy. Received on Mon Jan 17 2000 - 17:33:41 CST

Original text of this message

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