Relating fields by text instead of ID

[btappan]

Is it a bad practice to have tables related by a text field, specifically in this case alpha numeric part numbers like ABC123H, ABC124H, XYZ12C, etc. It would make importing data much easier, based on the fact that if I go by the Auto-numbered ID, I don't have the ID available for each part number until I've already imported some of the data. Is there a significant performance degradation by relating via "short text" datatypes? I'm just trying to follow some best practices guidelines while coming up with schema for the database structure. I could easily still have the tables have an Autonumber ID, but just not use it for table relationships.

[ranman256]

You can relate by text field.
It doesnt have to be a key.

[Rainlover]

As a personal preference I always use Autonumber as the PK on every table.

Also I name the field as, for example PKCars or PKBottles.

The foreign key would be FKTrains etc

Every table should have a PK.

[June7]

I have used text field as PK/FK and have no issues. However, performance might get slower with very large dataset because numbers index faster.

[Rainlover]

Is it a bad practice to have tables related by a text field, specifically in this case alpha numeric part numbers like ABC123H, ABC124H, XYZ12C, etc. It would make importing data much easier, based on the fact that if I go by the Auto-numbered ID, I don't have the ID available for each part number until I've already imported some of the data. Is there a significant performance degradation by relating via "short text" datatypes? I'm just trying to follow some best practices guidelines while coming up with schema for the database structure. I could easily still have the tables have an Autonumber ID, but just not use it for table relationships.

I was just wondering which way you went. Number or Text

[June7]

Are you having to import multiple related tables?

I had situation where I had to import data from multiple databases that used autonumber PK. Had to write a bunch of VBA code to maintain relationships.

Then another situation importing from multiple databases that used text fields as PK/FK. This certainly did make import much simpler.

[Rainlover]

Are we talking import or update.

Importing should be easy with either Text or Number.

Updating is another story.

Regardless of what you are doing I would still recommend Autonumber as the Primary key. It is so much easier to relate to other tables using a unique number rather that Text which may not be unique.

[ssanfu]

I too always use an autonumber type field as the PK field in tables. And I use a suffixs of "_PK" and "_FK". Much easier to see how tables are linked and a lot easier to select linking fields when setting up a main form-sub form link.
See Microsoft Access Tables: Primary Key Tips and Techniques

[Peter M]

I know this thread is a little old, but I would be very interested if people have any performance tests showing that an alpha-numeric primary key is slower than an autonumber key.

Personally I have not seen a difference and use text-PKs all the time. As the original poster noted, if you are importing data, it is much easier to use an existing ID as the primary/foreign key.
I ran a simple query to see if I could find a difference with the following setup:

Base_Table - 6.5 million records
TextID (Short text, 17 characters) - Unique Primary ID
GrouperField (short text, 255)
GrouperAmount (currency)

LastUser_Table - 1.3 million records
TextID (short text, PK)
Name (short text, 255)

Query:

Join Base_Table and LastUser_Table on TextID
Sum GrouperAmount by Name

I made a table that was a replica of Base_Table plus an auto-number field as the PK. There was no discernible performance difference, even when I applied manipulation to the TextID like Group By mid (TextID,7,5).

(Now to be fully transparent, none of these queries took more than 2 minutes to complete so it wasn't a really good test.)