A simple Architectural Question !!

[Bernard Piette]

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

 

[Allen Browne]

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

 

[Bernard Piette]

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]

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.

 

[Bernard Piette]

Well that's it I'll go a mix of both natural and autonumber primary keys
according to the best use case scenario for each table.

You will find the following to be my understanding of your recommedations.

Allen you said try it out so here it is. Did I follow correctly ?

C:\Documents and Settings\Administrator\Desktop\RDX 18-12-05 Antoine
December 21, 2005
v2.mdb
Table: tblAddresses Page: 1
Columns
Name Type Size
AddressID Long Integer 4
CountryName Text 50
ProvinceOrStateName Text 50
CityName Long Integer 4
PostalCode Long Integer 4
StreetAddress Text 50
AddressName Text 50
Relationships
tblPostalCodestblAddresses
tblPostalCodes tblAddresses
PostalCodeID PostalCode
Attributes: Not Enforced
RelationshipType: One-To-Many
tblCitiestblAddresses
tblCities tblAddresses
CityID CityName
Attributes: Not Enforced
RelationshipType: One-To-Many
tblProvinceOrStatetblAddresses
tblProvinceOrState tblAddresses
ProvinceOrStateName ProvinceOrStateName
Attributes: Not Enforced
RelationshipType: Indeterminate
tblCountriestblAddresses
tblCountries tblAddresses
CountryName CountryName
Attributes: Not Enforced
RelationshipType: One-To-Many

Table: tblCities Page: 3
Columns
Name Type Size
CountryName Text 50
ProvinceOrStateName Text 50
CityName Text 50
CityID Long Integer 4
Relationships

tblCitiestblLocations
tblCities tblLocations
CityID CityName
Attributes: Not Enforced
RelationshipType: One-To-Many

tblCitiestblBuildings
tblCities tblBuildings
CityID CityName
Attributes: Not Enforced
RelationshipType: One-To-Many

tblCitiestblUnitPromisetoPurchase
tblCities tblUnitPromisetoPurc
CityID SignatureCity
Attributes: Not Enforced
RelationshipType: One-To-Many

tblCitiestblAddresses
tblCities tblAddresses
CityID CityName
Attributes: Not Enforced
RelationshipType: One-To-Many

Table: tblCities Page: 4
tblProvinceOrStatetblCities
tblProvinceOrState tblCities
ProvinceOrStateName ProvinceOrStateName
Attributes: Not Enforced
RelationshipType: Indeterminate

Table: tblCountries Page: 5
Columns
Name Type Size
CountryName Text 50
CountryOfficialName Text 50
CountryAbbrev Text 2
CountryDomainExt Text 3
Relationships

tblCountriestblProvinceOrState
tblCountries tblProvinceOrState
CountryName CountryName
Attributes: Not Enforced
RelationshipType: One-To-Many

tblCountriestblAddresses
tblCountries tblAddresses
CountryName CountryName
Attributes: Not Enforced
RelationshipType: One-To-Many


Table: tblLocations Page: 6
Columns
Name Type Size
LocationID Long Integer 4
LocationName Text 50
CountryName Text 50
ProvinceOrStateName Text 50
CityName Long Integer 4
StreetAddress Text 50

Relationships

tblCitiestblLocations
tblCities tblLocations
CityID CityName
Attributes: Not Enforced
RelationshipType: One-To-Many

Table: tblPostalCodes Page: 7
Columns
Name Type Size
PostalCodeID Long Integer 4
PostalCode Text 8
Relationships

tblPostalCodestblAddresses
tblPostalCodes tblAddresses
PostalCodeID PostalCode
Attributes: Not Enforced
RelationshipType: One-To-Many


Table: tblProvinceOrState Page: 8
Columns
Name Type Size
CountryName Text 50
ProvinceOrStateName Text 50
ProvinceAbbrev Text 3
Relationships

tblCountriestblProvinceOrState
tblCountries tblProvinceOrState
CountryName CountryName
Attributes: Not Enforced
RelationshipType: One-To-Many

tblProvinceOrStatetblAddresses
tblProvinceOrState tblAddresses
ProvinceOrStateName ProvinceOrStateName
Attributes: Not Enforced
RelationshipType: Indeterminate

tblProvinceOrStatetblCities
tblProvinceOrState tblCities
ProvinceOrStateName ProvinceOrStateName
Attributes: Not Enforced
RelationshipType: Indeterminate

Thaks Again,

Bernard Piette

 

[Allen Browne]

Bernard, I didn't go through that in detail, but the general idea seems on
track other than the choice not to use enforced relations.

Relationships should almost always have enforced referential integrity, and
in most cases the foreign key field should also have its Required property
set to Yes.

 

[Joe Cilinceon]

I'm using a similar setup with states and coutries only. I don't use a city
lookup table which is what I use these for on forms only.

tblState
[StateID] * Number
[State] Abbreviation
[State Name] Full Name of state
[CountryID] Number


tblCountry
[CountryID]* Number
[Country Name] Full name of country

 

[TC]

here's a note from utteraccess and wondered what you thought about it...

"Also I do not recommend composite Primary keys"

That would come as a surprise to hundreds of thousands of commercial
database developers all over the world!

There is absolutely nothing wrong with composite primary keys. They are
the natural choice for child tables in parent/child relationships. It's
nonsense to say that they have any bad performance impact.

Of course, "everything in moderation". If I had 10 levels of 1:m
relationships, I'd certainly be reluctant to have a 10-field PK in the
bottom-most table! I'd probably introduce an autonumber PK, at some
point in that structure. But it's rare to go anywhere near that deep,
in my opionion.

HTH,
TC