Storing Foreign Key in a table

[Guest]

I was wondering after reading some comments about table design. Is it wrong
to use a lookup in a table to store the text of the foreign key to make the
table easier to read when in datat sheet view? I am a student and have been
using a lookup in my tables because it is easier to initially input data into
the table if you don't have to keep checking to see what the value of a
foreign key is. Also will using a lookup prevent a combo box on a form from
displaying a value. My combo boxes load but do not display a value.

 

[BruceM]

The use of lookup fields in tables is discouraged by most developers for a
number of reasons you can go into if you wish, although it may be simplest
for now to agree that lookup fields are to be avoided. Lookup tables are an
entirely different matter.
I can't make out what you mean by storing "the text of the foreign key to
make the table easier to read when in datasheet view." A few possibly
relevant thoughts:
You don't enter the foreign key field; rather, it is populated because of
its relationship to the primary key field (which is in another table). A
form / subform is the usual way to accomplish this.
A combo box based on a lookup field will in most cases work like a combo box
you create by other means. In any case, in order for a combo box to display
a value as you navigate from one record to another it needs to be bound to a
field or populated by code.
It would be best if you describe what you are doing, both in real-world
(non-database terms) and by describing the structure and relationship. If
the structure and relationships part is the question, just describe what you
are trying to do.

 

[Allen Browne]

In your lookup table, why not use the text as the primary key?

For example, if you have a table of categories, make a field named
CategoryID of type Text, and type the actual category names into this
column. Now in your other table, you can have a CategoryID field of type
text, use a combo that is fed from the Category table, and chose the
category from the text in the combo.

There is really no need to have a hidden autonumber at all. Problem solved.

If you do use a hidden autonumber, and limit the RowSource of the combo so
in shows only a subset of the categories available, in a continuous form you
will see the combo go blank on other rows. That's beause the value it needs
to show it not even in the RowSource query, so it has nothing to show. But
if you don't hide the bound column, Access can show the actual value, and
the problem is gone. So, if the bound column is the text, you don't have
this problem.

It is wrong to put 2 fields in your table that both show the value from the
lookup table: one to show the number, and the other to show the text. That
would be a recipe for inconsistency between the two.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
news:[email protected]...

 

[Guest]

I have a table tblPlayers, with 23 fields, 6 of them are foreign keys, such
as CityID, StateID, ZipcodeID, etc.

As an example in the field properties for CityID under lookup I have;
Display Control ComboBox
Row Source Type Table/Query
Row Source SELECT tblCity.CityID, tblCity.City FROM tblCity;
Bound Column 1
Column Count 2
Column Heads No
Column Widths 0";1"
Limit To List Yes
List Widths Auto
List Rows 8

In datasheet view there is only one column for each foreign key, it is just
easier to see the data in the table.

Will this affect the way values are displayed in the combo boxes on the form.

 

[Allen Browne]

What fields are in your State table?
Do you have:
StateID Text
Or do you have:
StateID AutoNumber
State Text

Since the State text is guaranteed to be unique (You can't have 2 Texas
states!) you don't need the autonumber. Just use the text field as your
primary key.

The same is true of your Zipcode table: the text of the zipcode is unique.

The same is probably true of your City table: Just use the City name as your
primary key. Then change these properties of your combo:
Row Source SELECT tblCity.City FROM tblCity;
Column Count 1
Column Widths {leave this blank}

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message

 

[John Vinson]

The same is probably true of your City table: Just use the City name as your
primary key.

<g> Just don't try that with Springfield in a United States dataset -
or for that matter, Los Alamos in New Mexico (there are two towns of
that name there).

Nitpick, otherwise of course the Coach would do very well to follow
your excellent advice!

John W. Vinson[MVP]

 

[Guest]

We were taught that all tables should have an auto number as the primary key
and then text. Is one way right or wrong or better or worse to use?

 

[John Vinson]

We were taught that all tables should have an auto number as the primary key
and then text. Is one way right or wrong or better or worse to use?

That's often the source of quasi-religious arguments supporting one or
the other viewpoint.

My opinion is that a Primary Key should meet three criteria: it MUST
be unique; it should be stable; and it's nice if it's short.

The two-letter code for state names meets all three criteria (in fact
it's half the length of a long-integer autonumber): TX always means
Texas; the codes very rarely change (well, Canada changed the code for
Labrador a few years back, and added one for Nunavut). Zip code are
almost as good. And if you use the actual codes in your table, you're
saved the need to join a second table to see the actual value, making
your application smaller and faster.

Some lookups are probably much better with a surrogate key, such as an
Autonumber - people's names are neither unique, nor stable, nor short,
for example; but there are people I respect who insist on using
"natural" keys (say, lastname + firstname + middlename + suffix +
address + city) with Cascade Updates set. I'll generally just use an
autonumber though.

I would have to disagree that "all tables should have an autonumber
primary key" however. If you have a good natural key - *use it*!

John W. Vinson[MVP]

 

[jahoobob via AccessMonster.com]

Since ZIPs are unique, if you have a list of all the ZIPs and their City and
State (I found a file somehwere on the net, but have forgotten where) you
could store just the ZIP.
BTW, if one of the MVPs who has a web site and would want to provide this
table, email me at jahoobob at yahoo dot com.

 

[Guest]

Since ZIPs are unique, if you have a list of all the ZIPs and their City and
State (I found a file somehwere on the net, but have forgotten where) you
could store just the ZIP.
BTW, if one of the MVPs who has a web site and would want to provide this
table, email me at jahoobob at yahoo dot com.

 

[David F. Cox]

Are Zip codes guaranteed to be fixed? I ask because we have been using UK
Postal areas with that belief, only to discover that they are subject to
change and even to going out of use.

David F. Cox

 

[John Vinson]

Are Zip codes guaranteed to be fixed? I ask because we have been using UK
Postal areas with that belief, only to discover that they are subject to
change and even to going out of use.

They are not; as populations move around, and towns grow and shrink,
zipcodes do indeed change. For instance, the town of Meridian, Idaho
near me recently went from having one zipcode to four (after
quintupling in population).

That's really a bit of a different issue though; it's not as if
everyone in Zip 83785 suddenly gets reassigned to 83740. It's a one
to many split, typically, which is new data which must be entered as a
change of address.

John W. Vinson[MVP]

 

[John Vinson]

Since ZIPs are unique, if you have a list of all the ZIPs and their City and
State (I found a file somehwere on the net, but have forgotten where) you
could store just the ZIP.

Well... no.

Many zip codes refer to more than one city, especially in crowded
suburban areas where there is a patchwork of city jurisdictions all
jammed together. The Post Office does have a 'preferred' city, but
often there are more than one "acceptable" cities.

Try going to www.usps.gov and using the Zipcode search for 90701 or
90601 just for examples.

John W. Vinson[MVP]

 

[Guest]

Well I would like to thank everyone for their comments. I guess it depends on
the way you are taught or what your personal preferences are. Until now I
wouldn't think of craeting a table without using autonumber as the PK but
what you all say does make sense in some cases.

Will have to bring it up in class this fall and see what kind of response I
get from the professors. I would assume if that was the way they were taught
then it is the way they prefer to teach.

Thanks for all the commnets

 

[Craig Alexander Morrison]

Whatever you take away from this discussion please bear in mind that whether
or not you use an AutoNumber for PK you MUST always declare the Natural Key
(should it exist) as a UNIQUE NON NULL INDEX.

Defining the Natural Key as the Primary Key will create just such an index
as well.

Whilst many (including I) frown upon the indescriminate use of AutoNumber
PKs as long as the Natural Key is defined as above you will ensure that your
database does not have duplicate values.

Remember a record containing a PK which is an AutoNumber with no other
UNIQUE NON NULL INDEX can contain duplicate records all bar the invented
AutoNumber.

--
Slainte

Craig Alexander Morrison
Crawbridge Data (Scotland) Limited

Small Business Solutions Provider

BTW For the record I do not use AutoNumbers for Surrogate Keys but that is
not for this discussion.

 

[jahoobob via AccessMonster.com]

ZIP codes ARE fixed. However, an address isn't fixed to a ZIP. 30303 has
been and will always be an Atlanta, GA zip unless they change the city's name.
They may add a ZIP to a city. I work in a city that had one ZIP. They added
another ZIP that also covered the entire city but they gave street addresses
the original ZIP and PO Boxes the new ZIP.
As to you previous post about the uniqeness of ZIPs, I know of what you speak
about two cities with the same ZIP. I live in one, 32571. But, if you
addressed a letter to me and put the city in whichI I don't live, I would
still get the letter. Domino's knows where to deliver my pizza and FedEx my
packages. If I order from Amazon and put in my ZIP, the Accepted (USPS) city,
not mine, appears and I still get my order. The ONLY area where this is a
problem is if you try to find my house and you know nothing of the area you
would go to the wrong town to look but you wouldn't find it. If you put my
street and zip in Streets&Trips you'll see where I actually live (maybe a few
houses off.)
That being said, my ZIP table does have an Autonumber PK, however, the ZIPs
are unique and if you use ZIP to fill in the City and State, you can always
change the city as I store teh ZIP not the PK.
BTW, to everyone, ZIP is the correct spelling as it is an acronym just like
UPS.