You will certainly find people who hate AutoNumbers (the "Always use a
natural key" group, and the "AutoNumbers are unreliable in JET 4" group),
and others who *only* use AutoNumbers (to be consistent (esp with WHERE
conditions that involve a key value), and the performance argument.)

There is no doubt that string operations are slower than numeric operations
at the CPU level. But for most database operations, the IO is the bottleneck
not the CPU, so the argument is rather academic.

There's another argument that text-keys use more disk space. At the cost if
disks, that's a non-issue. The increased size does increase the amount of
IO, so you want to keep your text keys short. 24 characters is generally
enough.

But instead of theorizing about it, try it. Run some timing tests. You will
see the performance issue is academic rather than real.

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

"Bernard Piette" wrote in message
...
Hi allen,
Thanks for your prompt reply.
Yup, that makes sense to me for Country it's resolved I will follow that
logic but here's a note from utteraccess and wondered what you thought
about
it...

"
I recommend you use Autonumbers for your PK in all your tables ...
The Text PK will not Index as fast as a Numeric value ...

I can not agree with your statement .. "An artificial key seems like
unnecessary overhead to me".
Also I do not recommend composite Primary keys ...
If you want the combination of the 3 fields City + StateID + CountryID to
be
unique .. then you need to make these 3 fields a compound (composite)
Index
... I would add an Autonumber field in this table for the PK ...

When using lookup tables .. you store the record ID from the record in the
lookup table ...
You can reference any information stored in the lookup table for the
specific record by including the lookup table in a query with the correct
relational join.
RDH
--------------------
Ricky Hicks
Microsoft MVP
Birmingham, Alabama USA
"

So what do you think?

Also, I don't think I can use same models for province and or city because
it is possible their would be doubles or do the dbl and triple keys take
care
of that.

Also any opinion about lookup fields in tables are they as bad as everyone
says??
Thanks a bunch,

Bernard Piette



"Allen Browne" wrote:

No. You don't have to have both a CountryID field and a Country field if
you
want to. You could just use the full name of the country as the CountryID
(Text-type primary key field.)

The problem with that approach is that the full name of some countries do
not make good choices for drop-down list selection: long names, names
starting with The, and names such as "People's Republic of China" (the
technically correct name for Taiwan.)

I therefore suggested that you use 2 *text* fields in tblCountry:
- an abbreviated name as primary key (Text, not AutoNum), and
- the full name of the country.

The table would then contain records like this:
CountryID Country
------------- ----------
Congo Democratic Republic of the Congo
Taiwan People's Republic of China (Taiwan)
USA United States of America

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

"Bernard Piette" wrote in message
...
With the help of Allen Brown we've come up with this idea.

Here's a simpler suggestion that identifies country, state/province +
country, and city + state/province + country:

tblCountry
*CountryID* Text abbreviated name
Country Text full name.

tblState
*StateID* Text abbreviated name
*CountryID* Text f.k. to tblCountry.CountryID.
State Text full name.

tblCity
*CityID* AutoNum primary key
City Text
StateID Text ) f.k. to tblState
CountryID Text )

Primary keys are marked with asterisks. I've suggested using natural
keys
for the first 2 tables rather than autonumbers. An artificial key seems
like
unnecessary overhead to me, and this also solves the interface issue of
the
disappearing values when the bound column is hidden and you filter your
combos.

You could a natural key in the 3rd table as well: City + StateID +
CountryID. That would work well, you may well *want* that combination
in
your related tables. However the 3-field key can start to get unweildy
if
you then have other tables related off that that become 4- and 5-field
keys.
So, while you certainly want a unique index on the combination of those
3
fields, I've suggested the artificial key as I imagine that all sales
data
end up relating back to the CityID at some point.

Question IS ? ...

Do i really need an country ID since there is a known 255* or so limit
and
by default no two countries will EVER EVER have the same name, do I
reallly
need to have CountryID, won't Country alone suffice...

The reason I ask leads to my second question,
When in my other tables i create fields for let's say Country or
province
my
programmer wants me to have the lookup store the names not the IDs, is
that
correct, and if I do it that way won't all my reports show country 33
for
Canada vs showing the actual name of canada.

What should I be storing in my lookups fields for each of Country
Province
City in bout 10 tables the ID "33" or the name "quebec" for example...

Or... should I be leaving these fields as text in the tables and use
lookups
only in my forms for said fields of Country State and City which I
find
in
about 10 or 15 tables and subsequent forms...

While a little longwinded I just wanted to get my question across..

Thanks for all upcoming answers.

Bernard Piette