• Post Reply Bookmark Topic Watch Topic
  • New Topic

Folders-and-files-like relationship Design. How ?  RSS feed

Ken Loh
Ranch Hand
Posts: 190
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi All,

I'd like to develop something which has a concept like the folders-and-files found in conventional file systems.

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 topics and many other subgroups.

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 its subcategories to be listed as well. That means I need NOT traverse to every descendant category to go through all items under them. I want to view all items in Electronics and its descendant subcategories at one glance.

I guess I would need some sort of pagination to view the resultant long list. I think I will resort to using CachedRowSet, or an arraylist of a Value-list handler to contain the items.

If I started out with two tables - CategoryTable and ItemTable:

- how should I design the DB tables relationship?

- I would be most likely to use a DAO to get the CachedRowSet or the value-list handler. How should the SQL query be constructed ?

Any help in whatever form would be very much appreciated.


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

The ItemTable
- itemId(PK)
- categoryId (FK pointing towards CategoryTable.categoryId. Many-to-one relationship)
[ April 05, 2005: Message edited by: Ken Loh ]
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!