On Wed, 27 Oct 2004 14:52:17 +1000, Helen Borrie <helebor@...> wrote:

>
> At 09:28 AM 27/10/2004 +0530, you wrote:
>
> >On Wed, 27 Oct 2004 10:48:38 +1000, Helen Borrie <helebor@...> wrote:
> > >
> > > 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.

two tables i'm dealing with:
t1: (
id integer (PK),
name varchar,
parts smallint,
...(30 fields describing the object in name)...
)

t2: (
id integer (FK-> t1.id),
part smallint,
size smallint,
wt, etc. (+5 fields),
)

i've got an insert and update trigger ensuring that t2.part <=
t1.parts for t1.id = t2.id.

each t1.name object can have several parts...and each of those parts
have their own descriptions (7 fields). t1 has approx 30 fields, one
of which is the total number of parts (t1.parts) that that obj has.
so, if id=1 has 5 parts, there will be a row in t2 to describe each of
it's 5 parts, so total 5 rows for id=1. now, if id=2 has 3 parts, we'd
have (2,1...), (2,2...), (2,3...), each being the
(id,part,other_fields...) of a row.

t2.part is being used as a serial counter for each items parts. so
(id=1,part=1...) and (id=2,part=1...) have no relation to each other.
they are just the 1st parts of items id=1 and 2. also, (id=1, part=2)
combo can not occur more than once, hence unique.

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

t1 would be the parent, t2 is the child. except for t2.(id,part) there
is no unique combo. all t1.id's will have atleast a part=1 and each
items parts are counted from 1 since they are independent of other
objects' parts.

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

it's a one:many relation. as described in t1 and t2, it's normalized.
if i use a sequence number to use as a unique id (like student ids), i
would have a proper pk. i'd need a 3rd table...

so newt2: (
seq_no smallint pk,
(5 fields of original t2)
)

and t3: (
seq FK -> newt2.seq_no,
id FK -> t1.id,
part smallint
)

...that maps seq. num in t2 to t1.id and part. is that what u had in mind?

i'd used this in an another situtation but wasn't sure of it's
benefits. does the speed that the "better PK" index provides justify
the design? or is it only a better design with not much speed
benefits? (which is fine with me)

anirudh