--- Osvaldo Sommer <[EMAIL PROTECTED]> wrote: > Yeap, that's the idea. > > That's call normalization. If you are interested in > database design, you > need to read Codd's book in database.
I thought I had a natural talent for database design, since I've done so much work with data, but it isn't as simple as I thought. > If you want, you I can help you looking at the > database you build and > give you some pointers. That would be great, thanks. But I'll probably spend a few days creating a new database first. I'd really like to figure out how to access GIS files and extract their data into MySQL tables. I've been told you can do it with Microsoft Excel, but I just have the Microsoft Works spreadsheet. (I should have spent the extra $50 when I bought my computer?) It looks like all the information I need has probably been organized by the GIS community, but that's another technology to learn about. > Osvaldo Sommer > > -----Original Message----- > From: David Blomstrom > [mailto:[EMAIL PROTECTED] > Sent: Saturday, May 29, 2004 9:39 PM > To: [EMAIL PROTECTED] > Subject: RE: Getting Oriented: Political versus > Ecological Geography > > 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 > === message truncated === __________________________________ 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]
