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 -> DB Design question: Do you create a separate PK in the join table?

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

From: <gmei_at_my-deja.com>
Date: Wed, 12 Jan 2000 21:30:54 GMT
Message-ID: <85irqe$lar$1@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 Wed Jan 12 2000 - 15:30:54 CST

Original text of this message

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