Hi Louis,

> I use a generated key in a many-to-many situation when there is a
> possibility of duplication. If Bob has more that one shirt in his
> closet, you might want an entry for each in the Wardrobe table. If you
> key on Person and Clothes, this would generate a key violation.

In this example, I have to disagree with you on this.

If Bob has more shirts, each shirt should be identifiable by itself. So
adding
another row would (for example) give:

> Table Clothes:
>
> 1 - Shirt
> 2 - Trousers
> 3 - Shoes
> 4 - Tie

5 - Another Shirt

And it should, because the number is the PK here (see other tables). The
number here itself is an artificial primary key.

I would rather say there's nothing wrong at all with compound primary keys,
but that using a column with a MEANING (like artikle codes, part numbers
etc) should be avoided. I'd rather use an artificial key ...

Using compound pks is fine and should be used.

With regards,

Martijn Tonies
InterBase Workbench - the developer tool for InterBase & Firebird
Firebird Workbench - the developer tool for Firebird
Upscene Productions
http://www.upscene.com

"This is an object-oriented system.
If we change anything, the users object."

> Svein wrote:
>
> #
> Use a generator to populate the PK, never use
> composite primary keys.
> #
>
> What's the reason for that?
>
> Whenever I've implemented many to many relationships I've used a
> composite
> primary key - it seems the most natural thing to do. e.g.
>
> Table Person:
>
> 1 - Jim
> 2 - Fred
> 3 - Bob
> 4 - Sam
>
> Table Clothes:
>
> 1 - Shirt
> 2 - Trousers
> 3 - Shoes
> 4 - Tie
>
> Table Wardrobe (many to many on Person, Clothes)
>
> personkey - clotheskey
> 1 - 2
> 1 - 3
> 2 - 1
> 2 - 2
> 3 - 3
> 3 - 4
> 4 - 2
>
> Table Wardrobe (many to many on Person, Clothes) with generated key
>
> genkey - personkey - clotheskey
> 1 - 1 - 2
> 2 - 1 - 3
> 3 - 2 - 1
> 4 - 2 - 2
> 5 - 3 - 3
> 6 - 3 - 4
> 7 - 4 - 2
>
> What's the purpose of the generated key - to query the table (who owns
> trousers?) you are going to have to refer to person and clothes. Isn't
> the
> addition of genkey going to make the query statements somewhat
> convoluted?
> Is there a big performance win that makes it worth it?
>
> Have I missed something or misunderstood (I'm not a database expert).
>
> Thanks,
>
> Ray