CODEWITHSUNDEEP
ms-accessprimary-keyprimary-key-design
arbitel
arbitel

Reputation: 321

Autonumber vs. Text String for primary key?

I'm trying to find the best solution to an issue I've just come across. I hate doing things without understanding so I'm hoping someone can help.

I have an Access database with a table that stores Hotel Information - and then another table that stores Itineraries. The Itineraries table will select from the list of Hotels on the Hotels table.

I want to make a proper relationship, but using an Autonumber primary key on the Hotels table that connects to the Hotels field on the Itineraries table won't work. (because the Autonumber ID doesn't match the hotel names.)

Is it better to:

A. Use the Hotel name as the primary key on the hotels table, even though the string length may get pretty long?

B. Change the display control on the Hotels field on the Itineraries table to a combobox that lists the Hotels table autonumber primary key - but hides it. Instead it shows the column with the hotel names. I found that solution here: http://www.trigonblue.com/accesslookup.htm

Neither solution seems perfect as I think solution A may slow the indexing down with long text strings, and solution B gets messed up if new fields are inserted in the table.

I'd hate to pick the wrong answer here and have problems down the road.

Can anyone help me out here? Please let me know if I need to clarify any part of my question.

Thanks!

Upvotes: 2

Views: 4546

Answers (3)

Has
Has

Reputation: 13

With the current fast computers and for a few tens or may be hundreds of records it's of no apparent difference to use a text or numeric PK, but certainly for many thousands of records AND ABOVE then the issue will be different, numeric is the friend of the CPU, because it is the easiest data type to be worked with by the processor. If I suppose that a table will have many thousands of records then I shall use Numeric and preferably of a long type.

Upvotes: 0

ThunderFrame
ThunderFrame

Reputation: 9461

You should almost never use a name as a Primary Key. Using a Unique ID in the form of a CODE or ID is a much safer approach. Avoiding the use of name allows you to:

  • Abstract the name from the identifier
  • Store the name in a single location
  • Change the name, if required, in a single location
  • Use less disk-space and memory.
  • Perform faster indexing, inserts, deletes, joins, sorts and group-bys.

Sometimes you'll have a code or ID already, or you're constrained by an internal/external rule, but most of the time an AutoNumbered Primary Key is very useful. It is:

  • Numeric, so it's stored efficiently
  • Numeric, so it's fast to work with
  • Guaranteed to be unique
  • New entries always insert at the end of a table and require minimal effort for page movements or index changes.

Upvotes: 3

Mike Hoxter
Mike Hoxter

Reputation: 46

Auto-number is the most efficient way to set up a Primary Key, it is the least work for a DBMS to search through to find what it's looking for. This is especially true if you are going to have Primary/Foreign key relationships in your tables.

Not to mention, there are advantages to doing it this way for storage purposes and indexing purposes (not a big deal on Access, but on others it would be).

Upvotes: 2

Related Questions