Re: Question on Primary Keys

As your implementation of primary and foreign keys are a representation of your compagny's reality, the answer to your question dependst on that reality. Is a user unique within a organisation unit, which means that one user can belong to several organisation units? In that case you have to go with both user id and org_unit_id in the primary key. But in that case it might even be better to have a table org_unit with PK org_unit_id, a table user with PK user_id and an extra table user_org or org_user with a PK user_id and org_unit_id, since it is a n:m relationship.

If a user is unique within the entire organisation than go for user_id as PK.

Hope this answers your question.

Reinier.

<rs> wrote in message news:sul4969os4o141_at_news.supernews.com...
> Hi all,
>
> I have a question regarding database schema design.
>
> Let me set up my question with some sample tables
>
> ORG_UNIT
> -------------------------------------------
> ORG_UNIT_ID NUMBER (PK)
> ORG_UNIT_DESC VARCHAR2(20)
> -------------------------------------------
>
> USER
> ---------------------------------------
> USER_ID NUMBER (PK or part of PK)
> ORG_UNIT_ID NUMBER (FK or patrt of PK)
> USER_NAME VARCHAR2(20)
> ---------------------------------------
>
> Each USER belongs to an ORG_UNIT.
>
> There are two ways to model this in the physical database model
>
> (a) The USER table can have a combined PK of USER_ID & ORG_UNIT_ID
>
> or
>
> (b) The USER table can have a PK of USER_ID (some sort of unique number)
> and have ORG_UNIT_ID as a FK.
>
> What advantages/disadvantages are there to each approach ?
>
> Can anyone point me to/provide me some practical pointers on how the
 unique
> PKs can be set up
> in Oracle if scenario (a) were to be chosen as the way to go.
>
> Thanks,
>
> Raju
>
>
Received on Mon Oct 16 2000 - 01:23:21 CDT