| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: DB Design question: Do you create a separate PK in the join table?
Yes your are right.
Every table in every database should have 1 primary key.
I.e. an replication under oracle does not work correcty on tables without
pks.
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.
--
 |
 |