Hi,

Will appreciate any guidance with this.

In my application each record will have a category column represented by a number. But since there are sub categories, searches should be able to find records to the category in question or any sub categories that belong. Since sub-categories may move around I do not want to write all sub-category into each record.

Assuming I’m searching for any records with category 7, which also has sub categories 11, 39, 56:

OPTION 1 (many OR's):

Store just one number in the column, and run:
SELECT WHERE (cat=7 OR cat=11 OR cat=39 OR cat=56)

OPTION 2 (text search):

Store text of all the numbers in the column:
SELECT WHERE Contain( '-'&cat&'-', '-7-11-39-56-')
*NOTE: what is the syntax for doing this?

For any one search the number of subcategories will very from none to up to 20. Most likely they will be from 1 to 5 though. This will be the most popular search in the application.

Which of the above option is preferable? Any other options I have not considered?
Thank you!

how many levels of subcategories will there be?

whatever you do, do not even think about option 2

Cat 1
-Cat A
---Cat X
---Cat Y
---Caz Z
-Cat B
---Cat V
---Cat W

If they search on Cat 1, it should return any of the categrories below it. I doubt there will be many searches with more than 20. Most of them will be between 1 and 10 (I hope). Is there another option you would suggest?

your last example shows 3 levels

are you sure there will be up to 10 levels?

Oh, I doubt the number of levels will go beyond 7. In most cases however it will only be up to 3 or 4. The number of sub-categories to search on would be much higher than the number of levels though.

it doesn't matter how many subcategories, only how many levels of subcategories

have you ever written a self-join with 7 copies of the table?

and "doubt" is not precise enough -- if you can have 8 levels deep, then you need 8 copies of the table

No I have not and am not familiar with how to do that. Would that be more efficient than option 1?

For now the max number of levels will be 5. As the application grows the number of level could grow, but I can always go back to the code and change the select statement to adjust for more levels.

could you please give the table layout so that i may see the table and column names

tbl_books: (bookID,bookName,catID)
tbl_categories: (catID,catParentID,catName)

tbl_categories example:
1 Business
2 --Marketting
3 ----Advertising
4 ----Product Placement
5 ----Product Placement
6 --Computers
7 ----Excel
8 -------Beginner
9 -------Advanced
10 ----Word
11 -------Beginner
12 -------Advanced


There would only be about 100 categories. My plan was to place an array in application memory
that stores all the sub categories for each category. So when a search is done on #7 (excel), from the
array I would get 7,8,9, and would perform a search on WHERE (catID=7 OR catID=8 OR catID=9).
(This would not apply to searching on category 1, business, which is equivalent to all)

The search can be done at any level. I would prefer not to write all of the levels into each book record
since sub-categories may move around.

here you go, the query for three levels, i'm sure you can extend it to four, then five, etc.

Wow, thank you for all the help and attention Rudy.

I need to ask though: are x amount of inner joins and unions more efficient than x amounts of (catID=7 OR catID=8 OR catID=9) ?

Thank you.

no, they're not, but you're comparing apples and oranges

consider how you get the 7 or 8 or 9, where did they come from? how many queries do you need to find all the subcategories and subsubcategories of a given category? how much data do you have to pull to find them all?

best thing is, you try it both ways and do the timings

OMG, did i really write + for concatenation? wrong dbms, rudy!

in mysql, you need to use the CONCAT function

in fact, you can actually do it nicely with CONCAT_WS --

SELECT CONCAT_WS(' > ',c.catName,sc.catname,ssc.catname) as breadcrumb ...