Trading One Inefficient Design for Another?

[CeCelia]

Your Assistance please..for another clueless newbie

BASICS: Access 2000 & DW4 on Win 2000 Adv Server. Once this database
is completed, some of the tables will be used the web for Search
functions only. All editing will be done in-house.

SCENARIO: This is my first "real" project in Access, and I am against
some heavy deadlines with data accumulating faster than I can enter it
all - because I keep tripping over myself in Access. I started out
with one table that ended up being huge and cumbersome. So I broke it
up into smaller tables, and resolved a number of redundancy issues.
BUT now I'm wondering if I have the same problem in reverse ie. too
many too small tables.

GOAL: Permit multi-function searches to web-user. For ex: all service1
vendors in CityX, all services1-10 vendors in MetroY, or all
service1,3,5 vendors in the Carribean. Search by vendor, service,
region.

RECORDS: currently 198 countries and 30,000 vendors.

CURRENT TABLES:

GeoRegions (14 records)
*RC Region Code
Region Region or Continent

GeoCountries (198 records)
*CC Country Code
Country Country or Nation
RC RC from GeoRegions Table

GeoStateProv (80 records)
*SC State Code
StateProv State or Province
*CC CC from GeoCountries Table

Now all of these work - showing the subforms/links (we are not *even
at the web-search part yet).

BUT when I create GeoMetros (1058 records)..
*MC Metro Code
Metro Metro Area
*SC SC from GeoStateProv Table - it does not show as a subform,
or link even though it shows in the Relationships window.

QUESTIONS:
Why isn't the GeoMetro working (the table not the car ;-)

I now have four tables for the geo info alone, is this too many? Am I
doing this all wrong? What is the most efficient way?

I don't understand when you need to create a linking table listing all
the values. If I need to create one, do you link to the PK of each
table to formulate the columns? or enter manually? And then what
tables do you link to/from?

I don't "get" what a foreign key is.

Is there a limit to how many many-to-many relationships a table can
have?

My goal of course is "quick concise to the point" (rather unlike this
posting ;-).

Thank you so much for your help & God Bless. I truly appreciate your
input/suggestions/verbal spankings.

 

[Graham R Seach]

Cecilia,

This is what you have:
GeoRegions
RC (Primary Key)
Region

GeoCountries
CC (Primary Key)
Country
RC (Foreign key to GeoRegions.RC)

GeoStateProv
SC (Primary Key)
StateProv
CC (Foreign key to GeoCountries.CC)

GeoMetros
MC (Primary Key)
Metro
SC (Foreign key to GeoStateProv.SC)

I assume when you talk about "subforms", you actually mean sub-tables.

<<is this too many? Am I doing this all wrong? What is the most efficient
way?>>
Given the number of records, you're design is the most efficient way of
storing it all. What you have will work provided you have the correct
datatypes PLUS data in the tables. If there's no data in GeoMetros, then
that table won't show up.

Let's assume GeoRegions.RC is numeric (long integer). In that case,
GeoCountries.RC must also be a long integer. Similarly, if GeoCountries.CC
is a long integer, GeoStateProv.CC must be a long integer, and if
GeoStateProv.SC is a long integer, then GeoMetros.SC must also be a long
integer.

<<I don't "get" what a foreign key is.>>
In simple terms, a "Foreign Key" is a copy of a primary key in another
table.

<<Is there a limit to how many many-to-many relationships a table can
have?>>
No.

Graham R Seach
Microsoft Access MCP, MVP
Sydney, Australia

 

[jfp]

The confusion is in the use of "key".
A foreign key in Table B is a field in B that is related (ref integrity)
to the primary key in Table A. In B, it is NOT a key. Consider
"foreign key" to be abbreviation for "a key in a foreign table".
-=-=

 

[CeCelia]

Ahhh .. ok I think I got it .. Was thinking that I had to "set-up" a
foreign key like you do a PK, not that a key "became" foreign by
virtue of linking it.

In simple terms, a "Foreign Key" is a copy of a primary key in another

Ack! Wait .. does this mean that you can *only link from Primary Keys?
Or was it explained this way because that's the way I did it?

Thank you so much!

 

[CeCelia]

Hello Graham & Jfp, Thank you so much for your responses.

I assume when you talk about "subforms", you actually mean

sub-tables..If >there's no data in GeoMetros, then that table won't
show up.

Yes, sub-tables. (I don't have the lingo down yet ;-). I had entered
two records in GeoMetros as a test - and the sub-table didn't show up.

Let's assume GeoRegions.RC is numeric (long integer). In that case,
GeoCountries.RC must also be a long integer...

ACK! Would you please clarify for me .. All PKs (or any fields you
link to/from) in a database have to be the same kind of data? Egads
I'm heading for trouble.

So then Remaining Questions:
And the inevitable New Question:
Regarding: auto# PKs vs "meaningful" ones.
Initially I used auto#. But then for ex: when opened GeoStateProv - it
was a PIB because just seeing #14 (CC) didn't tell me anything because
I can't remember all the #s. So I had to keep the GeoCountries table
open at the same time so that I knew what #14 was. So then I tried
also linking to Country in GeoCountries. THAT's when I ended up with a
whole whack of redundant information (does all this repeat info take
up space/time?). So I changed my PKs to "meaningful ones", All the RC,
CC, and CS are internationally recognized two or three digit codes
(most of which we already know).

BUT - I am using auto-number for Vendors, orders etc. Am I heading for
trouble down the road? Because the PKs will not be the same type of
data (text + integer).

I truly appreciate any assistance. Thank you.
CeCelia

 

[Graham R Seach]

Cecilia,

Foreign keys must be the same datatype as the key they relate to. If they
don't, Access (or any other database for that matter) won't understand how
to match them up.

The debate (read "argument") about "numeric keys" vs "natural keys" will
continue to rage long after you are I are dead. It's up to you which model
you adopt, and in which cases you adopt them. Just make sure that you use
the same datatype for PK-FK relationships!

Graham R Seach
Microsoft Access MCP, MVP
Sydney, Australia

 

[CeCelia]

Got it! Thank you JFP! Sincerely appreciate the assistance, and your time.