SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Addict
    Join Date
    Dec 2007
    Posts
    348
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    how to best implement dynamic sub-categories

    Hi all,

    How do you all implement dynamic sub-categories of items, e.g. products in a shop?

    For example you may have an item belonging in:
    computer -> software -> games -> shoot em up (1)
    or
    computer -> misc (2)

    Example (1) is in a 'third-level' subcategory whereas item (2) is just at a 'first-level'. How can these be implemented in a database where you may have variable sub-category depths?

    Obviously a variable amount of tables (sub-category, sub-sub-category, sub-sub-sub-category) is inefficient.

    So what I thought of doing was just having one table: categories

    ID - name
    ----------
    1 - computer
    2 - software
    3 - misc
    4 - games
    5 - shoot em up

    And then having another table showing the relationships between categories:

    Code MySQL:
    CREATE TABLE category_relationships (
       cat_id int NOT NULL,
       sub_id int NOT NULL
    )

    Where both id's would be foreign keys to the 'categories' table defining which is a sub-category of a given category (or sub-category) i.e. one such record might be:

    Code PHP:
    Array(
       [cat_id] => 4
       [sub_id] => 5
    )

    Showing that 'shoot em up' is a sub-category of 'games'.

    Then the product table would have a foreign key to whichever category or sub-category that product belonged to.

    My problem is how to get the 'tree' of categories, i.e. if I know a product belongs in the 'shoot em up' category, how do I recursively fetch each preceeding sub-category?

    I.e. how would I write a query which would fetch all the categories in between up to the root category: games, software, computer.

    Obviously I could make a simple recursive function server-side to fetch the previous category until there were no more categories (we'd reached the 'root') but this involves multiple queries. Is there anyway to do this in less queries, or more efficiently? With this table structure, or any other!

    Any help is apprecated!

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,510
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    If each subcategory can only relate to one 'higher' category, then the second table is superfluous. A parentid field in the categories table is enough.
    As far as creating the tree is concerned, there are already lots of posts about that. Try a search. There's also a word for that type of query, r937 posts it all the time, but I don't remember

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    the parent_id design is called the adjacency data model

    more information here: Categories and Subcategories
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,510
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by r937 View Post
    the parent_id design is called the adjacency data model
    See
    Now if I could only remember that word...

  5. #5
    SitePoint Addict
    Join Date
    Dec 2007
    Posts
    348
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    First, thank you both for your help!

    Rudy, that article was very informative, great work, but I have a question: you say that if the hierarchy is fifteen levels deep, we'd have to code a query with fifteen self-joins - that suggests to me the number of levels is somehow predetermined. What if I was creating a database for an online shop and allowed the user to determine the categories/subcategories (i.e. by creating a new node and specifying its parent node) - this way the depth could be infinite.

    Would it be better to limit the hierarchy depth in advance? Or should we generate the query in a server-side language each side a new level is created? (or for that matter, deleted) By this I'd mean that if a user entered a new category which created a new level, the system would find the stored SQL and alter it to include a further self-join.

  6. #6
    SitePoint Addict
    Join Date
    Dec 2007
    Posts
    348
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    See
    Now if I could only remember that word...
    well, I hadn't heard of it before now so that's one up on me!

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    as i said in that article, if you have a hierarchy that is 15 levels deep, you may want to reconsider whether it's really user-friendly

    me, if i have to drill down through 15 levels in order to find what i'm looking for, i'm outta there long ago

    so, yeah, use your app to limit the number of levels -- do it for your users!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Addict
    Join Date
    Dec 2007
    Posts
    348
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    okay, thanks Rudy!


Bookmarks

Posting Permissions

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