From: Matt Chatterley (zen31329zen.co.uk)
Date: Fri Jul 02 2004 - 13:52:37 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

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_blomstromyahoo.com]
> Sent: 02 July 2004 03:13
> To: mysqllists.mysql.com
> 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=zen31329zen.co.uk

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]