IIRC, "SQL For Smarties" has some excellent advice on how to handle tree
structures in an SQL database efficiently, by treating nodes as sets
("set" in the mathematical sense). You can efficiently do queries along
the lines of "get me everything in category X or any of it's
sub(-sub)-categories."
-JF
> -----Original Message-----
> From: Brent Baisley [mailto:brent@;landover.com]
> Sent: Wednesday, November 06, 2002 11:44 AM
> To: Walter D. Funk
> Cc: [EMAIL PROTECTED]
> Subject: Re: hierarchical Structure in Mysql
>
>
> Your structure is going to be a lot like a message board:
> Message Board-> Topic->Reply->Reply->...
> Where you can reply to someone else's reply or reply to the
> main topic.
>
> Your setup would have two tables, a "category" table and a "product"
> table. You get your cascade by each category having a field
> to indicate
> the parent category. If the field is NULL or ) or something
> you decide,
> then you've it a "main" category. The "final" subcategory
> then has the
> product id field populate which links to the product table.
>
> ProductCategories
> --------------------------
> RecordID
> ParentID (contains the RecordID of another record)
> ProductID
>
> Products
> ------------
> ProductID
> Product
>
> Categories
> ---------------
> CategoryID
> Category
>
> This structure will give you unlimited levels, although it will be
> harder to query since you have to traverse the tree to get all your
> information. It ends up being about 1 query for each
> sublevel. You can
> do it in one query with joins and table aliases, but that
> gets limiting
> as your levels grow.
>
>
> On Wednesday, November 6, 2002, at 12:43 PM, Walter D. Funk wrote:
>
> > I need to design a database, under a hierarchical
> structure. This Mysql
> > database aims to store a catalog
> > in which I should be able to define:
> >
> > CATEGORIES -> SubCategories -> Sub-SubCategories -> Product
> >
> >
> --
> Brent Baisley
> Systems Architect
> Landover Associates, Inc.
> Search & Advisory Services for Advanced Technology Environments
> p: 212.759.6400/800.759.0577
>
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
> <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
