Hmmmmm... I think you were an even bigger help than I anticipated! If I understand correctly, I need to fix the tables I've already created.
Consider three tables - Continents, Nations and States - which look something like this: CONTINENTS North America | New World | West [Hemisphere] | cna (code for North America) NATIONS United States | Washington, D.C. | us (code for the U.S.) | cna (links U.S. to North America in the Continents table) STATES Alaska | Juneau | ak (code for Alaska) | us (links Alaska to the U.S. in the Nations table) If I understand correctly, it would be smarter to create FOUR tables, that look like this: CONTINENTS North America | New World | West [Hemisphere] | cna (code for North America) NATIONS United States | Washington, D.C. | us STATES Alaska | Juneau | ak FOURTH TABLE (Links everything together) cna | us | ak (North America > U.S. > Alaska) cna | us | az (North America > U.S. > Arizona) cna | us | hi (North America > U.S. > Hawaii) cna | us | wy (North America > U.S. > Wyoming) cna | ca | ab (North America > Canada > Alberta) caf | ken | (NULL) (Africa > Kenya) caf | tan | (NULL) (Africa > Tanzania) This table would have just four fields (columns) and would begin with about 250 rows - one for each nation - with another 50 rows for the fifty U.S. states, then more rows for Canada's provinces, Mexico's states, etc. If I'm on the right track, then I could also add U.S. counties to the mix... North America > United States > States > Counties However, since there are roughly 3,000 counties, it might be better to put them in a separate table, with rows that might look like this: cna (North America) | sd (South Dakota) | Tripp (county) cna | sd | Melette cna | sd | Sioux cna | sd | Belle Fourche Or would you advise adding the counties to the "Fourth Table," which would look something like this?: cna | us | sd | Belle Fourche ccna | us | sd | Sioux cna | us | sd | Trippe cna | ca | ab | (NULL) ceu | fra | (NULL) caf | ken | (NULL) | (NULL) The top row = North America > U.S. > South Dakota > Belle Fourche County The last row = Africa > Kenya, with the rows corresponding to states/provinces and U.S. counties left NULL. I just thought of one problem, though - there are several U.S. counties that have the same name. For example, several states have a "Washington County." But maybe I could just give all the counties numerical codes, or something like this - tx-1 (for Texas' first county). I wish I'd thought about this earlier. It sounds a lot better than my original plan! Thanks. --- Osvaldo Sommer <[EMAIL PROTECTED]> wrote: > For what i understand, what you need to do is create > this structure: > > Characteristic > K Char_Code > Char_Description > > Continent > K Con_Code > Con_Description > > Country > K Cot_Code > Cot_Description > Cot_Continent ( This is the code of a continent in > the table > continent) > > > Country_Charact > K Des_Country ( This is the code of a country in > the table country) > K Des_Characteristic ( This is the code of a > characteristic in the > table characteristic) > > > This way you can define the characteristics 1 time > and assign to a > country as many or as few as you need. > > You may want to use innob tables to create the > foreing key and to help > them inforce them. > > Hope this is usefull > > Osvaldo Sommer > > -----Original Message----- > From: David Blomstrom > [mailto:[EMAIL PROTECTED] > Sent: Saturday, May 29, 2004 5:57 PM > To: [EMAIL PROTECTED] > Subject: Re: Getting Oriented: Political versus > Ecological Geography > > --- Peter Brawley <[EMAIL PROTECTED]> > wrote: > > >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). > > Thanks for the link. I've been studying some of the > resources. > > I have an idea for another approach. If putting > "grasslands, forests, mountains" in one cell is bad > practice, then I may wind up with a table with > hundreds of fields. Imagine one field filled with > the > names of the world's nations. Then imagine another > field for tropical cloud forests, with three South > American nations checked. A few northern nations are > checked in the "Tundra" field, and so on. > > Obviously, there are going to be vast numbers of > cells > with no values at all. > > But what if I instead created several tables, one > for > each continent and listing only ecological regions > associated with that continent. > > For example, the North America table might have just > three rows, for the U.S., Canada and Mexico, with > the > following fields: > > tundra | boreal forest | eastern forests | Rocky > Mountain forests | eastern grasslands | Great Plains > | > Sonora desert | California coastal | Pacific > Northwest > (there are actually a lot more.) > > The Africa table would have far more rows, for > nations > from Algeria to Zimbabwe, with fields that might > look > something like this: > > Sahara Desert | Sahel | Ethiopian mountains | > tropical > forest | woodlands | savanna > > Thus, North America and Africa will share no fields > in > common. However, North America and Eurasia will both > share at least two fields - Tundra and Boreal > Forest. > > So, if I create such tables for each continent, will > I > later be able to make a join that pulls up tundra in > North America, Eurasia and Antarctica? Can I pull up > the general term "grasslands" for all continents, or > pull up Great Plains grasslands for just North > America? > > 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] > > > --- > Incoming mail is certified Virus Free. > Checked by AVG anti-virus system > (http://www.grisoft.com). > Version: 6.0.692 / Virus Database: 453 - Release > Date: 5/28/2004 > > > --- > Outgoing mail is certified Virus Free. > Checked by AVG anti-virus system > (http://www.grisoft.com). > Version: 6.0.692 / Virus Database: 453 - Release > Date: 5/28/2004 > > __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
