A simple Architectural Question !!

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