Storing Foreign Key in a table

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" wrote in message
...
On Wed, 9 Aug 2006 11:51:02 -0700, CoachBarkerOJPW
wrote:

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]

On Wed, 09 Aug 2006 23:25:00 GMT, "David F. Cox"
wrote:

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]

On Wed, 09 Aug 2006 21:36:19 GMT, "jahoobob via AccessMonster.com"
u12179@uwe wrote:

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]

You do hear that taught sometimes but why must a table have an AutoNumber?

A table must have a primary key, i.e. a field or combination of fields that
uniquely identifies reach record. If there is a simple natural key (such as
a category name), why add an artificial key like an autonumber?

Many tables have no simple natural key. For example, a natural key for a
Client table would probably consist of Surname + FirstName + Address +
Suburb + BirthDate. The birthdate has to be included because a father and
son at the same address can have the same name. But if do make those 5
fields combined your primary key, you are unable to enter someone unless you
know their birthdate. It's just not practical. Sometimes we see people using
SSN as the primary key, but you run into problems with that too, as people
change their SSN, there are duplicates (despite the govt's best efforts) and
there are problems with identity fraud. So, an artificial key (such as
AutoNumber) is really the only way to go for this kind of table.

Some developers eschew text-based keys becuase they want to write generic
functions that can work without any modification for any table, and you do
need to add extra quotes as the delimiters if the field is Text. It is a
consideration, but you can write these functions to accept a Where clause
instead of an autonumber value, and the code is much more generic (copes
with any combination of any number of fields of any type.)

So, my style is:
- Use natural keys when there is a simple, obvious choice (most lookup
tables.)
- Turn on cascading updates for these relations (renaming, correct spelling,
....)
- Use an AutoNumber when there is no simple, natural key.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"CoachBarkerOJPW" wrote in
message
...
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?

"Allen Browne" wrote:

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}

"CoachBarkerOJPW" wrote in
message
...
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" wrote:

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.

"CoachBarkerOJPW" wrote in
message
...
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.

Yes, there are issues here.

I assume that if Coach has separate lookup tables for City, State, and Zip,
that each item occurs once only in its own table. So, there will be only one
Springfield in the City table, even though several states have a
Springfield.

If there are separate tables like that, it helps prevent misspellings, but
it does not prevent invalid addresses such as:
New York, CA 90210
since each of those components is valid in its own right. A single table of
all the valid cominations would be a better solution, provided you can keep
it up to date. The post from jahoobob was offering such a table.

So, Coach, you might want to consider John's advice, and go with the
combined table, unless that sounds like overkill for what you need.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"John Vinson" wrote in message
...
On Thu, 10 Aug 2006 00:08:42 +0800, "Allen Browne"
wrote:

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]

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

"Allen Browne" wrote:

You do hear that taught sometimes but why must a table have an AutoNumber?

A table must have a primary key, i.e. a field or combination of fields that
uniquely identifies reach record. If there is a simple natural key (such as
a category name), why add an artificial key like an autonumber?

Many tables have no simple natural key. For example, a natural key for a
Client table would probably consist of Surname + FirstName + Address +
Suburb + BirthDate. The birthdate has to be included because a father and
son at the same address can have the same name. But if do make those 5
fields combined your primary key, you are unable to enter someone unless you
know their birthdate. It's just not practical. Sometimes we see people using
SSN as the primary key, but you run into problems with that too, as people
change their SSN, there are duplicates (despite the govt's best efforts) and
there are problems with identity fraud. So, an artificial key (such as
AutoNumber) is really the only way to go for this kind of table.

Some developers eschew text-based keys becuase they want to write generic
functions that can work without any modification for any table, and you do
need to add extra quotes as the delimiters if the field is Text. It is a
consideration, but you can write these functions to accept a Where clause
instead of an autonumber value, and the code is much more generic (copes
with any combination of any number of fields of any type.)

So, my style is:
- Use natural keys when there is a simple, obvious choice (most lookup
tables.)
- Turn on cascading updates for these relations (renaming, correct spelling,
....)
- Use an AutoNumber when there is no simple, natural key.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"CoachBarkerOJPW" wrote in
message
...
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?

"Allen Browne" wrote:

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}

"CoachBarkerOJPW" wrote in
message
...
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" wrote:

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.

"CoachBarkerOJPW" wrote in
message
...
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.

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.

"CoachBarkerOJPW" wrote in
message ...
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

"Allen Browne" wrote:

You do hear that taught sometimes but why must a table have an
AutoNumber?

A table must have a primary key, i.e. a field or combination of fields
that
uniquely identifies reach record. If there is a simple natural key (such
as
a category name), why add an artificial key like an autonumber?

Many tables have no simple natural key. For example, a natural key for a
Client table would probably consist of Surname + FirstName + Address +
Suburb + BirthDate. The birthdate has to be included because a father and
son at the same address can have the same name. But if do make those 5
fields combined your primary key, you are unable to enter someone unless
you
know their birthdate. It's just not practical. Sometimes we see people
using
SSN as the primary key, but you run into problems with that too, as
people
change their SSN, there are duplicates (despite the govt's best efforts)
and
there are problems with identity fraud. So, an artificial key (such as
AutoNumber) is really the only way to go for this kind of table.

Some developers eschew text-based keys becuase they want to write generic
functions that can work without any modification for any table, and you
do
need to add extra quotes as the delimiters if the field is Text. It is a
consideration, but you can write these functions to accept a Where clause
instead of an autonumber value, and the code is much more generic (copes
with any combination of any number of fields of any type.)

So, my style is:
- Use natural keys when there is a simple, obvious choice (most lookup
tables.)
- Turn on cascading updates for these relations (renaming, correct
spelling,
....)
- Use an AutoNumber when there is no simple, natural key.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"CoachBarkerOJPW" wrote in
message
...
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?

"Allen Browne" wrote:

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}

"CoachBarkerOJPW" wrote in
message
...
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" wrote:

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.

"CoachBarkerOJPW" wrote
in
message
...
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.

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.

John Vinson wrote:
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]

--
Message posted via http://www.accessmonster.com