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

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:29:57 CST