Re: Question about grouping, catagorization, etc...
From: Andy Hassall (andy_at_andyh.co.uk)
Date: 07/31/04
- Next message: Manuel Lemos: "Re: A native Windows binding for PHP"
- Previous message: Rubem Pechansky: "Re: A native Windows binding for PHP"
- In reply to: Anthony M. Saffer: "Question about grouping, catagorization, etc..."
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
Date: Fri, 30 Jul 2004 23:14:01 +0100
On Thu, 29 Jul 2004 22:07:04 -0700, "Anthony M. Saffer"
<anthony@NOSPAM.opensource-strategies.com> wrote:
>I'm stuck on a problem that I'm sure isn't that hard to solve. In fact, I've
>read the solution a few months ago but can't seem to find it again now (when
>I really need it). I am hoping someone can clue me in. Here's the problem:
>
>Let's say I have a table called 'tblCategories' which contains 5 records and
>is structured like this
>
>tblCategories
> ID CATEGORY_NAME SUB_CATEGORY_OF
> 1 Automobiles
> 2 Planes
> 3 Buick 1
> 4 Cessna 2
> 5 Trains
>
>Now, as you can see the "Buick" category is both a "upper level" category
>(which means it might have sub categories as well) and a sub category of
>category 1 (Automobiles). You will also notice that category 4 (Cessna) is
>the same.
>
>My problem is that I need to order these into
>category/subcategory/sub-sub-category links similar to the way Yahoo does
>their directory. So, on the output page, the output might look like this:
>
>Automobiles
> Buick
>Planes
> Cessna
>Trains
>
>Basically, I want the user to be able to "drill down" through categories.
>Can anyone clue me in on how to accomplish this?
Depends on your database, and what you want to optimise it for - easy updates,
or quick reads of the tree. Odds are it'll be read much more than written to.
Options include:
(1) A recursive function that fetches all children of a category, then all
children of then, issuing multiple queries until it's traversed the tree. Works
on your current schema, but is going to result in an awful lot of database
roundtrips as your tree grows.
(2) Use a hierarchical query SQL extension if your database supports it, e.g.
CONNECT BY in Oracle. As I work mostly with Oracle, this is what I tend to use.
Not many databases have extensions like this; MySQL certainly doesn't. Whilst
it's really quite similar to (1), it's all done in the database in one round
trip, which can give considerable savings.
(3) Read the whole lot in one go, and get PHP to sort out the tree structure.
Unlikely to scale well at all.
(4) Start denormalising your schema to make selects easier, at the cost of
making inserts more costly. Here's my take on the issue from a while ago:
http://groups.google.com/groups?selm=3ls1gukloommab2omash5lnovh5nun4ufa%404ax.com
(5) Use one of the clever tree storage schemes that are floating around, such
as Joe Celko's "nested sets" or "materialised path". Some
sample hits from Google on those terms:
http://www.dbazine.com/tropashko4.shtml
http://www.codeproject.com/database/nestedsets.asp
-- Andy Hassall <andy@andyh.co.uk> / Space: disk usage analysis tool http://www.andyh.co.uk / http://www.andyhsoftware.co.uk/space
- Next message: Manuel Lemos: "Re: A native Windows binding for PHP"
- Previous message: Rubem Pechansky: "Re: A native Windows binding for PHP"
- In reply to: Anthony M. Saffer: "Question about grouping, catagorization, etc..."
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
Relevant Pages
|
