Question on Primary Keys

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 - 00:23:56 CDT