Use the autonumber field for all Primary keys. There are various reasons for this, to name but a few:
Use of Autonumber in a multiuser environment is sometimes problematic if using sequential / incremental.
Jim said:
Primary keys are not 'for user consumption' they are designed purely for use by the db to Uniquely identify records ... Autonumber is just plain EASIER.
Agreed
the index must 're-jig' itself in order to fit the new value into it's index in the appropriate position
Interesting. You are saying that the index used by Access is sequential (vs btrieve and other indexing strategies), and the index has to be inserted inbetween other values to preserve the sequential sequence. Do you have a reference on this -- I would be interested in reading up in this further.
Jim said:
Using a serial number as a pk could lead to problems down the line. What if serial numbers completely change format further down the line, or even the company stops using these serial numbers for whatever reason.
What are your thoughts of using character based pk's? Customer or VenderCodes, ProductCodes, CourseCodes, etc.
Use of character based codes have the advantage of not having to include the supplemental table.
For example, in an Order Entry system where you want to find Orders for Product "XYZ"
Using the Autonumber PK...
SELECT tblProduct.ItemName, tblOrderDetail.*
FROM tblProduct INNER JOIN tblOrderDetail ON tblProduct.ProductID = tblOrderDetail.ProductID
WHERE tblProduct.ItemName = "XYZ";
vs use of a ProductCode...
SELECT tblOrderDetail.* FROM tblOrderDetail
WHERE ProduceCode = "XYZ"
Use of ProductCode resulted in not having to use the INNER JOIN.
I believe
BNPMike referred to this with his mention of good use of indexes and joins.
...But I think we may be missing the point.
Databases are used mainly for either inserts and for reporting, or a mixture of the two.
Insertions and deletions are slowed down by the number of indexes used on the table.
Queries (reporting and updates -- have to find the record or records) are improved by proper indexing.
Crowley16 - what type of database do you have?
The objective of your database will influence your indexing and primary key strategies.
...Moving on
Objective said:
...there's 20k records...
For large databases, indexing and design strategies become much more important.
For example, the technical level of understanding in this post is high. So we all know that by creating a form based on a table can result in awful performance if the table has a lot of records entered into the table. When the form is opened, the form retrieves all records in the table -- can generate a lot of network traffic, and can generate local desktop performance issues, especially if there is not much room in the TEMP work area. A better approach is to either to only bring other a few fields, or use an unbound form, and only bring other the record in question.
In many cases, these types of approaches to minimizing data movement will have a much bigger impact on performance than small changes to indexes.
I agree with Jim that this is a big topic with many valid views and perspectives.
Richard
