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

From: Emmanuel <zouzou_at_yahoo.com>
Date: 13 Jan 2000 16:47:47 GMT
Message-ID: <01bf5dee$c41b8240$3601017e@EHM.cirra.fr>

In my opinion, putting an independant PK is not a good idea.

Here's why :

You will probably want to select the list of accounts for a given user. So the column USER_ID in the UserAccount table will have to be indexed.

You will probably want to select the list of users for a given account. So the column ACCOUNT_ID in the UserAccount table will have to be indexed.

Added to the PK which is automatically indexed, this will lead to THREE indexes in your table. so :

Extra room needed to store the PK column itself;
Extra room needed to store the PK's index;
Last but not least, extra time needed for index updates when you delete, update, or insert rows in the table.

Let alone the index consideration, you will have to create a sequence to generate the PK numbers, which will increase your database complexity.

Moreover, on a functional point of view, this configuration does not guarantee the unicity of a link between a given user and a given account, unless you add a unique constraint (index) on ACCOUNT_ID+USER_ID (4 indexes) !!!!

If you just keep USER_ID and ACCOUNT_ID :

Declare a PK which consists of the two columns (so your table WILL HAVE A PK ANIWAY);
Put an index on the column which is in the second position in your PK. For example : if your PK consists of the column USER_ID and ACCOUNT_ID (in THIS order), the index related to the PK will be used when you select the list of accounts owned by a given user. So you have to create an index consisting of the column ACCOUNT_ID in order to select the list of users for a given account.

So in this configuration :

- Just two indexes instead of three.
- Less room needed on the database.
- Less time for index update.
- Less complexity (no sequence)
- The unicity of a link between a given account and a given user is

guaranteed.

gmei_at_my-deja.com a écrit dans l'article <85iro2$l8k$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.
>
Received on Thu Jan 13 2000 - 10:47:47 CST