[posted and mailed, please reply in news]
James (dragonzfang@hotmail.com) writes:
[quoted text, click to view] > 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.
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
