Essentially, I think, you are asking about organizing hierarchical data. This is something which I always find tricky to wrap my head around, but, one approach I seen used successfully is to use (initially) two tables:
1. A table such as 'Places' which contains the actual data (or Locations, any suitable name really) 2. A second table which contains the 'meta-data' - in this case, the organization of the hierarchy. So. Places might be: PlaceID, Type, Name, Description Places_Hierarchy might be: PlaceHierarchyID, PlaceID, ParentID Thus, for the example below.. Places: 1 state Arizona .. stuff .. 2 country USA .. stuff .. 3 country Japan .. stuff .. Places_Hierarchy: 1 1 2 2 2 NULL 3 3 NULL Then, you can join the two together and organize things that way. There are a number of other things you can try - like moving the 'type' out into a lookup table and storing the ID in the hierarchy (allowing you to retrieve all places of a certain type, for instance). This is a situation in which views are (for me, anyway) sorely missed! Cheers, Matt > -----Original Message----- > From: David Blomstrom [mailto:[EMAIL PROTECTED] > Sent: 02 July 2004 03:13 > To: [EMAIL PROTECTED] > Subject: Re: Parent-Child Relationship Question > > And here's a follow up question... > > After looking at my database from a fresh perspective, > I'm now thinking of combining tables "area" and > "family" into a single table. > > If I do that, it would make life so much simpler if I > had TWO name fields, like this: > > ID | Name | ParentID | Parent Name > -------------------------------------------- > az | Arizona | us | United States > us | United States | kna | North America > jpn | Japan | keu | Eurasia > > I could then slap a $mycode = 'az" on a page and > easily fill in its name and the name of its parent > without fiddling with queries, joins, unions, etc. > > I know that duplicating names in two fields isn't the > most elegant solution, but would create any major > problems? > > Thanks. > > > > __________________________________ > Do you Yahoo!? > Read only the mail you want - Yahoo! Mail SpamGuard. > http://promotions.yahoo.com/new_mail > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
