Re: Question on Primary Keys

If I'm not mistaken basic data-modelling goes like this:

Entity A
* DESCRIPTION Entity B
* DESCRIPTION We have A and B and there is a 1:n relation ship where existence of B is dependent on existence of A (so the relation from B to A is mandatory whereas the relation from A to B is not)

If you make a ER_diagram in Designer Oracle will generate this:

table A
#id (PK)
*description (not null column)

table B
#id (PK)
*A_id (FK, not null, so manadatory)
*description (not null column)

If an entity has attributes which make it unique, then you can use these attributes as a primary key. For example the entity 'man': Does name make it unique?
Does birthday make it unique ?
Does employee no. make it unique ?

No to those three questions.
However employee_no. might make it unique in a given environment (a compagny) so in that context employee_no is a valid primary key. If this man works for several departments then you might want to add a department column. But this department number doesn't make the man more unique, it just states that he works for several departments. Any reference to this department is not a primary key. If you did than you would also have to record additional info like name, birthday, salary etc. for EVERY department he works for.
NO! Instead of that, you create a table department_personal with two columns (Emp_id and Dept_Id).
In this case there is a combined primary key, which consists of two foreign keys, but that is because both attributes, describe the entity.

Reinier.

<rs> wrote in message news:sulkojfg90bac0_at_news.supernews.com...
> Well,
>
> Here is another example , I think it is a little easier to discuss :
>
> Table : USER - USER_ID (PK)
> USER_NAME
>
> Table : CALENDAR - CALENDAR_ID
> USER_ID
> .........
> .........
>
> The existence of CALENDAR IS DEPENDENT upon the existence of a USER.
>
> Now :
>
> I have heard from other sources that both CALENDAR_ID and USER_ID should
 be
> the combined primary key for the calendar table. (This is said to be the
> more formal approach to this type of situation.)
>
> However, it would also make sense to make CALENDAR_ID the PK and make it a
> unique sequenced number and then have USER_ID as the FK. This way, the
> CALENDAR_ID could be indexed very easily. Rather than indexing a
 combination
> of 2 columns , it seems easier to index a unique sequenced number.
>
> Any thought ?
>
> Raju
>
> "Reinier" <Reinier_Dickhout_at_hetnet.nl> wrote in message
> news:enJ#kq0NAHA.349_at_net025s...
> > If the existence of the user is depending on the existence of an
> > organisation unit you should make it a mandatory FOREIGN key, not a
 primary
> > key. Primary keys are intended to identify the uniqueness of a record in
 its
> > own table, foreign keys are intended to represent relationships between
> > different entities.
> >
> > Reinier.
> >
> > Jan Lenders <J.Lenders_at_Betuwe.net> wrote in message
> > news:8se93i$qm3$1_at_nnrp1.deja.com...
> > >
> > > > Each USER belongs to an ORG_UNIT.
> > > > (a) The USER table can have a combined PK of USER_ID & ORG_UNIT_ID
> > > > (b) The USER table can have a PK of USER_ID (some sort of unique
> > > > number) and have ORG_UNIT_ID as a FK.
> > > >
> > > Raju,
> > > If the existence of a USER object is dependent on the existence of a
> > > ORG_UNIT you should make the ORG_UNIT_ID (foreign key) a primary key
 in
> > > the USER table.
> > >
> > >
> > > Sent via Deja.com http://www.deja.com/
> > > Before you buy.
> >
> >
>
>
Received on Mon Oct 16 2000 - 06:19:33 CDT