David Blomstrom <[EMAIL PROTECTED]> wrote on 10/25/2005 06:23:52 PM:
> I hope this isn't too long-winded. It's kind of hard > to explain. > > I'm trying to figure out a scheme that will display > animal distribution information. First, I want to > divide animal species by continents. Next, I want to > further identify species that live on islands that are > associated with continents, like Japan (Continent = > Eurasia, Island = Japan). > > Now, imagine four species with the following > distribution: > > Eurasian bird - Mainland Eurasia, plus the British > Isles and Japan > Eurasian bird2 - Mainland Eurasia only > Island Bird - British Isles and Japan only > Japanese bird - Japan only > > I'm working on a content management system, so each > bird will have its own page. Tentatively, I'd like the > distribution for each bird to display like this: > > EURASIAN BIRD > Eurasia* > *Including British Isles and Japan > > EURASIAN BIRD2 > Eurasia > > ISLAND BIRD > Eurasia* > British Isles & Japan only > > JAPANESE BIRD > Eurasia* > Japan > > In other words, I always display a species' native > continent by default, followed by a more detailed > description if it also inhabits an island(s) or if it > lives on an island only. What's the best way to do > this? > > I thought one way to do that might be to make two > columns, one for the broadest distribution (e.g. > Eurasia), the other for the specific distribution > (e.g. Japan). Then I could make a separate row for > each place a species inhabits, as follows: > > SPECIES | DIST | DIST2 > Eurasian Bird | Eurasia | Eurasia > Eurasian Bird | Eurasia | British Isles > Eurasian Bird | Eurasia | Japan > Eurasian Bird2 | Eurasia | Eurasia > Island Bird | Eurasia |British Isles > Island Bird | Eurasia | Japan > Japanese Bird | Eurasia | Japan > > In the table above, every species listed would display > Eurasia, from colum DIST. Then I'd like to display > this additional information based on the data in the > last field, DIST2: > > Eurasian bird: "*Including the British Isles & Japan" > Island Bird: "*British Isles and Japan only" > Japanese Bird: "*Japan only" > > I should add that I already have geographic tables > that display parent-child relationships. However, > they're tricky to work with because of varying number > of steps between children and parents. For example, > Europe's parent is Eurasia, but the Philippines have > TWO parents - Asia, then Eurasia. > > Anyway, before I try to figure out how to display this > with PHP I want to make sure I'm using a workable > scheme. Any tips? Thanks. > > My suggestion: Don't use parent-child tables to organize your geographic information. Read these articles and see if it doesn't make better sense to organize your geographies into trees. Basically, you end up assigning each animal only to the set of "leaf" nodes where it lives. The structure can be as deep as you want it to be. That way if something only lives on the Galapagos islands, it automatically lives in Argentenia and South America because that's how the tree would be set up. Make sense? http://dev.mysql.com/tech-resources/articles/hierarchical-data.html http://www.sitepoint.com/article/hierarchical-data-database/2 Shawn Green Database Administrator Unimin Corporation - Spruce Pine
