What we did in our database design is create 3 fields

CodeID Quickkey Name
210000 1 Shirt
210001 2 Trousers
210002 3 Shoes

I understand the reasoning for this as like you said, the code shouldn't
have a meaning. The problem is that to the user it does have a meaning.
And to key data efficiently, it is much easier to use the code that has
meaning to the user. This is why we came up with the Quickkey. In all
our forms the user can either key in the Quickkey or Choose from a list
based on Name. However, the database gets populated with the CodeID.

As a side note. Our 'power users' who like to query the database found
it confusing to look at the data as it is populated with these internal
CodeIDS .... so we wroter triggers to populate the Quickkeys into the
data that are only used to do queries on.

Todd

Martijn Tonies wrote:

> 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
>
>
> To unsubscribe from this group, send an email to:
> ib-support-unsubscribe@egroups.com
>
>
>
> Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service
> <http://docs.yahoo.com/info/terms/> .

--
Todd Brasseur, A.M.A.A.
COMPASS Municipal Services Inc.