Re: Question on Primary Keys

On Mon, 16 Oct 2000 15:35:16 +0530, <rs> wrote:

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

While this might be a good idea logically in some instances, I don't think it's a good idea practically in these two instances. The problem with the Org Unit example is: what happens if the person changes Units? The constraints on a PK are 1. Not change 2. Be Unique.

You may have the same problem with the Calendar - if there's any way you could need to change calendars for the same user (or users for the calendar, which sounds silly unless you've been watching the way people are playing musical chairs in IT these days) then you'd have to change the PK, which could cause an update problems in other relationships.

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

That makes more sense to me. Both from a maintenance and performance standpoint. I think compound keys, while a neat idea from the design standpoint, are not that practical in real life. They may be a holdover from the old days when programmers were cheap and disk space was expensive.

hth;

DCraig. Received on Mon Oct 16 2000 - 08:46:07 CDT