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