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.



Louis Kleiman

SSTMS, Inc.



-----Original Message-----
From: Ray Drew [mailto:ray_drew@...]
Sent: Tuesday, December 10, 2002 8:51 AM
To: ib-support@yahoogroups.com
Subject: Re: [ib-support] Re: time series and FB/IB



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










__________________________________________________
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com

To unsubscribe from this group, send an email to:
ib-support-unsubscribe@egroups.com



Your use of Yahoo! Groups is subject to the Yahoo!
<http://docs.yahoo.com/info/terms/> Terms of Service.



[Non-text portions of this message have been removed]