Results 1 to 13 of 13
  1. #1
    Join Date
    Jan 2004
    Location
    Toronto
    Posts
    33

    Question Unanswered: Better design/select method for mult sub-categories search?

    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 Im 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!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    how many levels of subcategories will there be?

    whatever you do, do not even think about option 2
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2004
    Location
    Toronto
    Posts
    33
    Quote Originally Posted by r937
    how many levels of subcategories will there be?
    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?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    your last example shows 3 levels

    are you sure there will be up to 10 levels?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2004
    Location
    Toronto
    Posts
    33
    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.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jan 2004
    Location
    Toronto
    Posts
    33
    Quote Originally Posted by r937
    have you ever written a self-join with 7 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?

    Quote Originally Posted by r937
    and "doubt" is not precise enough -- if you can have 8 levels deep, then you need 8 copies of the table
    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.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    could you please give the table layout so that i may see the table and column names
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jan 2004
    Location
    Toronto
    Posts
    33
    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.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    here you go, the query for three levels, i'm sure you can extend it to four, then five, etc.
    Code:
    /* books in searched category */
    select bookID
         , bookName
         , c.catName as breadcrumb
      from tbl_categories as c
    inner
      join tbl_books as b
        on c.catID = b.catID
     where c.catName = 'Business'
    UNION ALL   
    /* books in searched category's subcategories */
    select bookID
         , bookName
         , c.catName 
           +' > ' +sc.catname as breadcrumb
      from tbl_categories as c
    inner
      join tbl_categories as sc
        on c.catID = sc.catParentID    
    inner
      join tbl_books as b
        on sc.catID = b.catID        
     where c.catName = 'Business'
    UNION ALL   
    /* books in searched category's subcategories' subcategories */
    select bookID
         , bookName
         , c.catName 
           +' > ' +sc.catname 
           +' > ' +ssc.catname as breadcrumb
      from tbl_categories as c
    inner
      join tbl_categories as sc
        on c.catID = sc.catParentID    
    inner
      join tbl_categories as ssc
        on sc.catID = ssc.catParentID    
    inner
      join tbl_books as b
        on ssc.catID = b.catID       
     where c.catName = 'Business'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Jan 2004
    Location
    Toronto
    Posts
    33
    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.

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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 ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •