RE: Parent-Child Relationship Question

[Matt Chatterley]

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:david_blomstrom@stripped]
> Sent: 02 July 2004 03:13
> To: mysql@stripped
> 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/mysql?unsub=1