I think this is pretty simple, but I'm not thinking
very clearly at the moment, and I want to make sure I
get it right. I want to create a database with
information on the world's nations and smaller
jurisdictions (e.g. states and provinces) AND on
physiographic and bio/ecological regions.
My primary table simply lists the continents and
oceans, and looks something like this:
North America | continent | New World | cna | 1
Pacific Ocean | ocean | Oceanus | opa | 8
where cna = Continent/North America and opa =
Ocean/PAcific
I'll probably add a few more fields, listing the size
(area) and highest and lowest elevations, for example.
This table will be cross-referenced with tables
focusing on nations, states and U.S. counties.
The second series begins with a table featuring
biogeographic realms, which are roughly comparable to
continents in general, but with some spectacular
exceptions.
For example, the Sahara Desert is obviously part of
Africa, not Eurasia. But biogeographers divide the Old
World into the Palearctic and Afrotropical realms,
linking the Sahara Desert to the Palearctic, which is
primarily Eurasian.
So I'm trying to figure out a scheme that will link
the Sahara Desert to both Africa (Continents table)
and Eurasia (Biogeographic table). Looking at it
another way, I need to link Africa (Continents table)
to both the Palearctic and Afrotropical realms. And I
need to link Eurasia (Continents table) to the
Palearctic and Indo-Malayan realms.
It gets a little trickier, because I'm hoping to match
countries and even states and provinces to specific
ecological regions, as mapped out by the World
Wildlife Fund.
To see where this is going, consider three rows from
three tables - Continents, Nations and States - linked
together by the code for North America, cna:
North America | continent | New World | cna | 1
United States | nation | [major regions] | cna | 1
Colorado | state | grasslands, Rocky Mountains | co |
cna | 5
* * * * * * * * * *
Next consider three rows from three tables that focus
on ecological regions:
Nearctic Realm | North America | South America | nea |
1
Biome6 | Grasslands | Nearctic | Neotropical |
Palearctic | Afrotropical | Indo-Malayan |
Australasian | 6
Great Plains | Grasslands | Colorado, Nebraska,
Wyoming, etc. | NA482
* * * * * * * * * *
The first three tables - Continents, Nations and
States - are working out quite nicely. I've joined
them, along with a fourth Counties table.
I can probably figure out the ecological series,
treating biological realms as continents,
biomes/habitat types as nations and ecological regions
as states and provinces.
But I'm a little confused about linking the two series
together. If I'm working with the political series -
Continents/Nations/States - and I'm focusing on
Colorado, I'd like to be able to pull up a list of all
the ecological regions in Colorado - Rocky Mountains,
Great Plains and Colorado Plateau. Conversely, if I'm
working with the ecological series, and I see that the
Great Plains extends into Colorado, I'd like to be
able to pull up a list of Colorado's big brothers, the
United States and North America.
I suspect it will all just come together after I make
the ecological series. But here's one thing I need to
know...
In the states table, should I list Colorado's regions
in three cells... Great Plains | Rocky Mountains |
Colorado Plateau, or group them in one cell, like
this:
Colorado | state | grasslands, Rocky Mountains,
Colorado Plateau | co | cna | 5
It gets even trickier, because I may link Colorado to
several regions, including ecological regions,
physiographic provinces and political.
Similarly, imagine a table row in my ecological series
that focuses on a really big ecological region, like
the Sahara Desert. In the row below, I included a
field for the Sahara's realm (Palearctic), another
field for its continent (Africa), and a third cell
that lists every nation in the Sahara.
PA0169 | Biome 05 | Palearctic | Sahara Desert |
Africa
| Egypt, Libya, Algeria, Tunisia, etc.
Obviously, I can't create a separate field for each
nation, as that would add about 250 columns to my
table, so this is the only way to do it, right?
Thanks.
__________________________________
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]
