Folder-and-file-like object relationship implementation. How ?

[Ken Loh]

Hi All,

I'd like to develop something which has a concept like the
folders-and-files in your PC.

A folder can have subfolders and/or files. The subfolders themselves
have the same characteristics .

This characteristic can also be found in Google Newsgroup, aka Usenet.
A group can have many postings and many other groups.

I want to reapply this concept for listing contents for categories,
not unlike the categories one can find in Yahoo. For example:

The root has many categories: Electronics, Art, Entertainment, etc.
Under Electronics there are subcategories such as cameras, washing
machine and refrigerators. However, here's the twist:

When I select Electronics, I want all items under all subcategories to
be listed as well. That means I need to traverse to every descendant
categories to view all items under them.

If I start out with two tables, CategoryTable and ItemTable, how
should I design their relationship and how should the SQL query be
constructed ?

Any help in whatever form would be very much appreciated.

P.S.:

The CategoryTable, in my mind, should have at least these columns:
- categoryId (PK)
- categoryParentI d (To maintain the hierarchy structure)

The ItemTable
- itemId(PK)
- categoryId (FK pointing towards CategoryTable.c ategoryId.
Many-to-one relationship)

[Bill Karwin]

Ken Loh wrote:

When I select Electronics, I want all items under all subcategories to
be listed as well. That means I need to traverse to every descendant
categories to view all items under them.

If I start out with two tables, CategoryTable and ItemTable, how
should I design their relationship and how should the SQL query be
constructed ?

SQL doesn't handle heirarchies in a very straightforward way. Oracle
and perhaps some other vendors have invented "recursive" queries to
handle this type of case, but they're not standard SQL. MySQL has no
such extension to the language.

Another method is described in Joe Celko's book "SQL For Smarties".
Instead of storing a parent id in the category table, make another table
that records all the ancestor-descendant relationships:

insert into pathenum (parent, child, pathlength) values
('Electronics', 'cameras', 1),
('cameras', 'digital cameras', 1),
('Electronics', 'digital cameras', 2),
('Electronics', 'refrigerators' , 1);
etc.

Now you can do joins to find all descendant categories of Electronics
(where parent = 'Electronics'), all immediate children (where pathlength
= 1), all ancestors (where child = 'digital cameras'), etc.

Once you have the set of categories you want, then you can do a join to
the ItemTable to get the items in all those categories.

Inserting and deleting members of the heirarchy takes some thought but
with some scribbling on paper to make sure you have covered all the
cases, you can do it.

Read Joe Celko's book for more information.

Regards,
Bill K.

[Ken Loh]

Thank you Bill for your enormously helpful feedback.

Thanks also for reading btw the lines in the paragraph which I made
several major mistakes which would drive other readers towards the
opposite direction. Firstly, I missed out the word NOT when I intended
to say "need NOT traverse". Secondly, thank you for choosing to
interpret correctly that what I really meant by "all subcategories"
was actually "all descendant categories".

Once again, a big 'THANK YOU' to you ! :)

Bill Karwin <bi**@karwin.co m> wrote in message news:<d2******* **@enews1.newsg uy.com>...

Ken Loh wrote:

When I select Electronics, I want all items under all subcategories to
be listed as well. That means I need to traverse to every descendant
categories to view all items under them.

If I start out with two tables, CategoryTable and ItemTable, how
should I design their relationship and how should the SQL query be
constructed ?

SQL doesn't handle heirarchies in a very straightforward way. Oracle
and perhaps some other vendors have invented "recursive" queries to
handle this type of case, but they're not standard SQL. MySQL has no
such extension to the language.

Another method is described in Joe Celko's book "SQL For Smarties".
Instead of storing a parent id in the category table, make another table
that records all the ancestor-descendant relationships:

insert into pathenum (parent, child, pathlength) values
('Electronics', 'cameras', 1),
('cameras', 'digital cameras', 1),
('Electronics', 'digital cameras', 2),
('Electronics', 'refrigerators' , 1);
etc.

Now you can do joins to find all descendant categories of Electronics
(where parent = 'Electronics'), all immediate children (where pathlength
= 1), all ancestors (where child = 'digital cameras'), etc.

Once you have the set of categories you want, then you can do a join to
the ItemTable to get the items in all those categories.

Inserting and deleting members of the heirarchy takes some thought but
with some scribbling on paper to make sure you have covered all the
cases, you can do it.

Read Joe Celko's book for more information.

Regards,
Bill K.