>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. Codd's first axiom is that all values shall be atomic (yes, mysql sets & enums break that rule). Putting your little set 'grasslands, mountains, plateau' in one column would make queries on those items awkward. You might want to bone up on normalisation (viz links at http://www.artfulsoftware.com/dbresources.html). FWIW, I found your description of your model a little hard to follow, eg I couldn't make out whether lakes would be in the same table as continents & oceans, or how you would model relationships like adjacent-to, surrounds, entirely-enclosed-by, disjoint, whether you are using openGIS, &c. PB ----- Original Message ----- From: David Blomstrom To: [EMAIL PROTECTED] Sent: Friday, May 28, 2004 5:46 PM Subject: Getting Oriented: Political versus Ecological Geography 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]
