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

> 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.

This is the option that I'd go with.  

> 2. UserAccountTable:
> UserAccountID (PK)
> User_ID (FK)
> Account_ID (FK)
>
> Here you still have FK constraints, but you add one independent PK.

If you have use for a UserAccountID, this would be an option too. But adding it does add additional complexity.  

> 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 do agree with the programmers on this one (based on the info you've given). Option 2 adds extra complexity. It's not that much to handle, but it is something extra. You'd probably have to set up a sequence to generate the UserAccountID.

Every table does not have to have it's own independent PK. That's what composite keys are for. As long as you have the mechanism to uniquely identify each row go ahead and use it. And you have that mechanism in option 1.

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

In option 2, you have a functional dependency (FD) that states UserAccountID-->User_ID,Account_ID. But UserAccountID is a "mythical" component. And to me, it's just asking for trouble to use this basis. It's easy to forget years later.

But in all, it's just my opinion based on the information you've outlined. If you have additional info, it may be the case that Option 2 is better.

HTH,
Brian Received on Thu Jan 13 2000 - 08:12:51 CST