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 3 3203
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: 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
[posted and mailed, please reply in news]
James (dr*********@hotmail.com) writes: 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, so****@algonet.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
James Lankford (dr*********@hotmail.com) writes: 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.
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.
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.
For this kind of example, I prefer to have (InvoiceNo, RowNo) as the
key for the child table.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Ilan Sebba |
last post by:
I am trying to add a record using SQL. My problem is that the primary keys
are foreign keys, and these foreign keys are autonumbers. I therefore do
not know the primary keys of the record I am...
|
by: Geoff Cayzer |
last post by:
At http://www.blueclaw-db.com/tips_tricks.htm I came across a section
which is included below and was hoping for some comment on the
article.
--------------
Almost never use this auto-number...
|
by: yf |
last post by:
A KB article
"http://support.microsoft.com/default.aspx?scid=kb;en-us;209599" tells
that the maximum number of records that a table may hold if the PRIMARY
key data type is set to AUTONUMBER is...
|
by: Thomas A. Anderson |
last post by:
I am a bit confused in creating a composite primary key. I have three table
with two of the tables containing primary keys. I have two of the tables
(each with a primary key) having one to many...
|
by: Usenet User |
last post by:
I have a datagrid bound to a dataset table.
Unfortunately, the table's primary key consists of not 1, but 3
columns, so I cannot correctly set the DataKeyField property of the
grid (it only...
|
by: LurfysMa |
last post by:
Most of the reference books recommend autonum primary keys, but the
Access help says that any unique keys will work.
What are the tradeoffs?
I have several tables that have unique fields. Can...
|
by: rAinDeEr |
last post by:
I have a Parts Table with the following structure..
I need to decide which makes the primary key of the table.
I cant have any sequences nor can I add any new fields in this Table.
Table...
|
by: Alan Mailer |
last post by:
A project I'm working on is going to use VB6 as a front end. The back
end is going to be pre-existing MS Access 2002 database tables which
already have records in them *but do not have any...
|
by: Danny |
last post by:
Hello,
We imported a bunch of tables from a database and realized that the
primary keys weren't copied to the destination db. In order to re-
create the keys, we need to know which tables have...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |