Automatic Primary Keys/AutoNumbers - How i want it!

[Im Called Al]

Hi All

Im trying to design a stock database.
I need several Primary Keys - Auto Numbers.

For example, i want the stock to relate to the supplier and to the type of
product. So say i have a supplier called DeskServe, and a Product number off
1253 which is a office desk, i want the autonumber to automatically generate
something like : DS1253OFDSK

does that make sense?
any idea?

 

[John Vinson]

Hi All

Im trying to design a stock database.
I need several Primary Keys - Auto Numbers.

For example, i want the stock to relate to the supplier and to the type of
product. So say i have a supplier called DeskServe, and a Product number off
1253 which is a office desk, i want the autonumber to automatically generate
something like : DS1253OFDSK

does that make sense?
any idea?

I'm sorry, but no, it doesn't.

Autonumbers ARE NUMBERS. They have one purpose, and one purpose *only*
- to provide a guaranteed unique key. You cannot assume that they will
be sequential, or that they won't have gaps; and you cannot put text
into them.

What you are describing is called an "Intelligent Key" - and that's
not a compliment. Storing multiple pieces of information (the
supplier, the product number, and the product type) jammed into one
field is simply bad design. Each field should be "atomic" - having
only one value!

If you have three fields which jointly identify an item - use the
three fields. You can concatenate them in a Query for display
purposes, but the composite field should NOT be stored in your table.

John W. Vinson[MVP]

 

[Leif]

Excellent summary John.

I have all my database design students take the oath:

"The key, the whole key, and nothing but the key -
So help me Codd"

 

[John Vinson]

Excellent summary John.

I have all my database design students take the oath:

"The key, the whole key, and nothing but the key -
So help me Codd"

:-{)

You and I must have had the same teacher... I took that oath back in
Oracle orientation, twenty years ago!

John W. Vinson[MVP]