I put a screenshot of my database organization online
at
http://www.geoworld.org/database.gif It features four
tables, focusing on Continents, Nations, States and
Counties. Notice that Continents and Nations share a
"CCode" (continent codes) column, Nations and States
share a NCode (nation codes) field, and States and
Counties share a SCode (state codes) field.
The Counties table in the screen shot has no code of
its own, but I've just added one that consists of the
state code plus some digits. Thus, every row under
Arizona will have a field with az for joining with
Arizona, plus something like az10048 for a county's
ID.
I also want to work in some natural areas -
physiographic provinces, ecological regions, etc. -
somehow. The Realms column in the Continents table is
a beginning.
The other scheme I had in mind was to create a central
table that features ID columns from each table. For
example, a header row and some sample row might look
something like this:
Continent Codes | Nation Codes | State Codes | County
Codes
cna | us | ak | ak10022
cna | mx | chi | NULL
opa | fp | NULL | NULL
These rows represent continent-North America > U.S. >
Alaska > an Alaskan borough
continent-North America > Mexico > Chihuahua
ocean-Pacific > French Polynesia
With this scheme, instead of linking Continents
directly to Nations, each table would be joined to the
central column, which would match ALL the
jurisdictions.
Which scheme do you think is best?
__________________________________
Do you Yahoo!?
Friends. Fun. Try the all-new Yahoo! Messenger.
http://messenger.yahoo.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
