At 13:23 10.12.2002 +0000, you wrote:

> > The series table could be a good idea, but don't use the series_id
>as part of the PK of another table. Use a generator to populate the PK,
>never use composite primary keys.
>
>Why? I think that index on "series_id" would have low selectivity, then.

Of course you can have an index with series_id, and there's nothing wrong
with an index (series_id, PK) - I just didn't like it being part of the PK.
When I learnt database programming at school in the late 80s, we used
composite keys, but when I started using Interbase in late 1998 I was told
that meaningless keys populated by generators was the way to go. Why? I
never actually asked that question, just started using such keys (as usual,
I haven't been 100% obedient in all cases). But I have made me some thoughts.

1) Meaningless keys mean less trouble if the meaningful, visible keys or
the database structure change
2) Atomic keys makes it simpler to link to other tables
3) Atomic keys are shorter, hence I suspect databases designed that way to
be marginally faster with joined queries
4) Ann and/or Helen says it is the way to go, and they normally know what
they are talking about ;o)

My reasoning is no argument to change any small system that works OK, but
for complex systems that isn't working as they ought to or is tedious to
maintain, I think one ought to stick to atomic keys rather than composite ones.

Set