At 09:28 AM 27/10/2004 +0530, you wrote:

>On Wed, 27 Oct 2004 10:48:38 +1000, Helen Borrie <helebor@...> wrote:
> >
> > At 03:42 AM 27/10/2004 +0530, you wrote:
> >
> > >hi
> > >i've got a table with a 2 column primary key: (id, part).
> >
> > My first question in this puzzle is:
> > why is this unstable "part" element in the primary key? If "id" on its own
> > is not unique, why is that so?
>
>well, "id" would be unique for each item...which has several "part"s.
>this is comparable to a phone number list. to keep things flexible
>(i.e. any number of phone numbers for one person) and to normalize. it
>would generally have 7-8 parts but it varies and can go higher. that
>was my attempt at avoiding bad db design.

You didn't avoid bad db design.

>my option would be to keep 8-10 cols in the main table where id is
>unique...which isn't good. there will always be that 1 in 50 scenario
>where i'd need 15 parts, so it makes sense to use another table
>(child).

Except that you are not using a parent-child structure here. You have only
children, with no parents, and redundancy. Additionally, choosing to use a
field that is both unstable and meaningful as part of the primary key
defeats the rule of atomicity.

>so "id" by itself isn't unique in that table but the combo is. also,
>in any usage with the table i'd either use just id (first part of the
>index) or id and part in that order, so the pk index gets used.

Then you have given yourself an integrity problem. This structure needs
normalising into parents and children. Depending on what constitutes a
"part" (is is something that is, or should be, in a "parts" table?) there
might be a many:many relationship to resolve, using an intersection table
of (parent) id, sequence number and part id.

./heLen