2

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!

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
arbitel
  • 321
  • 6
  • 22
  • You can still add a relationship between Hotels and Itineraries using the Hotel ID - just add an indexed long fiedl "HotelID" to the Itineraries with "Allow Duplicates" - MUCH better than linking to hotel names – dbmitch Sep 17 '16 at 22:49
  • Thanks for your response. Sorry I'm a novice and maybe not comprehending - wouldn't the Hotel ID be a number on the Itineraries field then? I wouldn't know which AutoNumbers correspond to the Hotel Names – arbitel Sep 17 '16 at 22:53
  • in order to answer your question, your option B is the way to go! its the safest and recommended way :) Reason: you are using the key, nothing but the key! :) :) – Krish Sep 19 '16 at 14:05
  • 1
    To use the Automnumber you would have to change the column in the Itineraries table to show the Automnumber values rather than the hotel names, then change you insert and update procedures for the Itineraries table to first lookup the Automnumber value in the hotel table. In a high activity environment where the number of hotels is small the performance gains promised by an integer PK/FK may not materialize and may not anyhow be worth the effort of changing your procedures. – onedaywhen Sep 19 '16 at 15:09

3 Answers3

3

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.
ThunderFrame
  • 9,352
  • 2
  • 29
  • 60
  • "Guaranteed to be unique" - Well, the autonumber can be reseeded back to 1 so that new rows will generate duplicates. Also, you can update the column that has the autonumber property to purposely create duplicates. The guarantee you claim is solely for Primary Key and nothing to do with AutoNumber. – onedaywhen Sep 19 '16 at 14:12
  • Except this is Access. Messing with the seed is possible using DDL, but it is [NOT recommended](http://superuser.com/questions/288087/how-do-i-set-the-first-value-of-autonumber-in-access#comment301363_288087) – ThunderFrame Sep 19 '16 at 14:24
  • "New entries always insert at the end of a table" - The same could be said of any table, whether or not it had an autonumber and/or a primary key. I think you mean that if the autonumber is incremental (not random) and the maximum value has not been reached then when the file is compressed then the physical ordering on disk is not changed because the PK dictates the clustered index. This is a dubious advantage because there is often contention for the most recently created rows: if they are on the same physical page then you may get locking issues. Random autonumber might be better. – onedaywhen Sep 19 '16 at 14:53
2

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).

Mike Hoxter
  • 46
  • 2
  • Thanks for your response - so is option B the best here? Or is there another way I can still make use of the Autonumber to set up the relationship? – arbitel Sep 17 '16 at 22:35
  • 1
    Not sure why you think "*solution B gets messed up if new fields are inserted in the table.*" That is the proper way to do it - and you just change your combo box to sort on the hotel name - nothing gets messed up in the display. You can still add an index to hotel name to make it quicker for sorting if you want – dbmitch Sep 17 '16 at 22:47
  • The only thing with that solution is you're choosing the Column Count and Column Width. So say the Key is column 1, and the display you want is column 2. You'd set the lookup tool to 2 columns and set the Width to 0";1". If you ever inserted a new field before the 2nd column, that would display instead. I mean, I guess I could remember to not insert a field, but it seems like there should be a better way. – arbitel Sep 17 '16 at 22:50
  • 2
    @arbitel: This is really a non-issue. Make your combobox rowsource `SELECT HotelID, HotelName FROM Hotels` and you can insert as many new columns into `Hotels` as you want, and where you want. – Andre Sep 18 '16 at 07:15
  • "[Auto-number] is the least work for a DBMS to search through to find what it's looking for" - it rather depends what it's looking for! Probably great for finding a single Auto-number value but if a user is searching for Auto-number values then the design is wrong. If the user if searching for, say, all hotel's whose name begins with the letter 'B' then an Auto-number Primary Key isn't best, hotel name as PK would be better. – onedaywhen Sep 19 '16 at 15:01
0

With the current fast computers and For a few tens or may be hundreds of records its of no apperant 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 shull use Neumeric and preferably of a long type.

hassanein issa
  • 13
  • 5