Choosing values for primary keys

[James]

Hello group:

I've done alot of reading on this subject somewhat and have found that
many people have many different opinions on this subject. My question
centers mainly around using a lookup table to enable users to select a
pre-defined list of values.

I have developed a practice myself of avoiding AutoNumber type data
fields for primary keys where the primary key will be related to a
child table. Nevertheless, what do most users do with lookup tables?
My thoughts are to create a small key value for each value in the
lookup table. For example:

I might have a Carriers table which shows a list of carriers that I
might ship an order by. One of the entries may be 'Air Freight -
Overnight', or 'Air Freight - 2nd Day Air'. I've seen a few examples
where the primary key field for each entry like these would be
autonumber, or at least, a numeric value. What I like to do is create
my own key, like for 'Air Freight - Overnight', I might use 'AFO' for
the key, and for 'Air Freight - 2nd Day Air', I might use 'AF2'. Any
thoughts on this? Mine are that even tho the users may never see this
value - I, as the developer will see it and I tend to prefer a key
value based on real data that means something other than an
auto-incremented number. In referencing the well-known Northwind.mdb
database, I noticed their Categories table used a number field value,
like 1, 2, 3....etc, but their customers table used values like
'ALFKI' to represent their key values.

What are some other thoughts out there? I'm working with Access
currently, but this project is about to move to SQL Server.


James

[gabe]

Re: Choosing values for primary keys

I can't speak from much experience (only actually created a few small
tables...) but in large tables, you'll save space using a numeric value
I think. A 32 bit value will give you LOTS of unique numbers for rows.
In your example, 3 ascii characters is still shorter (24 bits.)
However if you end up using lots of long-ish keys, you'll eat up lots of
extra bits.

However, you can see that I use lots of letters to say very little, so
who am I to comment on space?! :)

Just my $.02....trying not to lurk so much!

-gabe

James wrote:[color=blue]
> Hello group:
>
> I've done alot of reading on this subject somewhat and have found that
> many people have many different opinions on this subject. My question
> centers mainly around using a lookup table to enable users to select a
> pre-defined list of values.
>
> I have developed a practice myself of avoiding AutoNumber type data
> fields for primary keys where the primary key will be related to a
> child table. Nevertheless, what do most users do with lookup tables?
> My thoughts are to create a small key value for each value in the
> lookup table. For example:
>
> I might have a Carriers table which shows a list of carriers that I
> might ship an order by. One of the entries may be 'Air Freight -
> Overnight', or 'Air Freight - 2nd Day Air'. I've seen a few examples
> where the primary key field for each entry like these would be
> autonumber, or at least, a numeric value. What I like to do is create
> my own key, like for 'Air Freight - Overnight', I might use 'AFO' for
> the key, and for 'Air Freight - 2nd Day Air', I might use 'AF2'. Any
> thoughts on this? Mine are that even tho the users may never see this
> value - I, as the developer will see it and I tend to prefer a key
> value based on real data that means something other than an
> auto-incremented number. In referencing the well-known Northwind.mdb
> database, I noticed their Categories table used a number field value,
> like 1, 2, 3....etc, but their customers table used values like
> 'ALFKI' to represent their key values.
>
> What are some other thoughts out there? I'm working with Access
> currently, but this project is about to move to SQL Server.
>
>
> James[/color]

[Erland Sommarskog]

Re: Choosing values for primary keys

[posted and mailed, please reply in news]

James (dragonzfang@ho tmail.com) writes:[color=blue]
> I might have a Carriers table which shows a list of carriers that I
> might ship an order by. One of the entries may be 'Air Freight -
> Overnight', or 'Air Freight - 2nd Day Air'. I've seen a few examples
> where the primary key field for each entry like these would be
> autonumber, or at least, a numeric value. What I like to do is create
> my own key, like for 'Air Freight - Overnight', I might use 'AFO' for
> the key, and for 'Air Freight - 2nd Day Air', I might use 'AF2'. Any
> thoughts on this? Mine are that even tho the users may never see this
> value - I, as the developer will see it and I tend to prefer a key
> value based on real data that means something other than an
> auto-incremented number. In referencing the well-known Northwind.mdb
> database, I noticed their Categories table used a number field value,
> like 1, 2, 3....etc, but their customers table used values like
> 'ALFKI' to represent their key values.[/color]

In the system I work, we use both mnemonic codes and numeric keys
(which rarely are IDENTITY values, but we generate them ourselves).
But we do not pick them at random.

Basically, if the table is pre-loaded, that is we define the data in
the table, the key is a good. This is because we may have to refer to
the key value in our SQL code (or client code), and using numeric values
may easily cause errors.

On the other hand, if the data in the table is user-entered, the key is
numeric. Because who would generate the codes in this case? There are a
few tables with user-entered data where the key is actually a code,
but this is when there is a natural code to pick. Prime examples are
countries and currencies.

(There are also pre-loaded tables with numeric keys. But I didn't
design them. Or they were accidents. :-)

--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Erland Sommarskog]

Re: Choosing values for primary keys

James Lankford (dragonzfang@ho tmail.com) writes:[color=blue]
> In my Carriers table example, this is mainly just a lookup table, values
> are not likely to change often. If a new code needs to be defined, then
> the administrator can simply create his/her own unique key for the new
> entry.[/color]

We usually have a GUI for this sort of thing, but as you say, a lot this
data is highly static once it is in place.
[color=blue]
> In the case of header/detail, parent to child table examples, I can see
> where having an autonumber generated key value is very beneficial. The
> two tables would still be linked via an invoice number, for example -
> but yet the autonumber key ID would serve as the unique identifer for
> the row. If the table becomes corrupted and needs to be rebuilt, or
> exported to another table, then it doesn't matter if the ID #'s change -
> nothing else is really "depending" upon it, and it still serves to
> uniquely identify that row.[/color]

For this kind of example, I prefer to have (InvoiceNo, RowNo) as the
key for the child table.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp