Page 1 of 2 12 Last
  • Jump to page:
    #1
  1. For POny!
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Location
    Amsterdam
    Posts
    416
    Rep Power
    115

    Solid categories table schema draft?


    Hi guys,

    I am about to make a table where I store categories. I have a feeling this can be done with one table. Each category can have a parent category and children categories. hereunder is a mockup of what i think would be a good idea. love to hear any tips or advice.

    Code:
    /*
     * possible table schema
     */
    
    #   table   |   (categories)
    #   -------------------------------------------------------
    #   catID   |   mainID  |   name        |   description
    #   -------------------------------------------------------
    #   5       |   5       |   software    |   software desc
    #   20      |   5       |   frontend    |   frontend desc
    #   21      |   5       |   backend     |   backend desc
    #   44      |   20      |   css         |   css desc
    #   55      |   20      |   javascript  |   javascript desc
    #   60      |   55      |   jquery      |   jquery desc
    As you can see the category software has the same main ID as its catID, this would be the (super)parent.

    Does this look okay?

    P.s. for those interested, this will be part of a sort of task manager I am going to build. (with also other tasks than coding )
    Last edited by aeternus; February 17th, 2013 at 02:24 PM.
  2. #2
  3. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,904
    Rep Power
    1045
    Hi,

    this is a valid solution and probably the most obvious one. However, it has one big disadvantage: You need multiple queries (one for each level of the tree) to retrieve the subcategories for a given category. This is cumbersome and slow.

    Check the nested set model for an alternative approach, which only requires a single query for selecting subtrees. This article also has in-depth explanations of both solutions and the advantages and disadvantages of each one.
  4. #3
  5. For POny!
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Location
    Amsterdam
    Posts
    416
    Rep Power
    115
    Ha Jaques1,

    I am out of rep to give you, but I am as always glad with your response! I had a slight feeling this could be tedious Going to read those links you gave.
    Thanks! alot!!
  6. #4
  7. For POny!
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Location
    Amsterdam
    Posts
    416
    Rep Power
    115
    Oh wow,
    That depth field is absolutely brilliant. I could have thought of that

    Thanks again Jacques1!

    P.s. that second article is a bit more complex i'll do that in the morning

    Cheers!
  8. #5
  9. For POny!
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Location
    Amsterdam
    Posts
    416
    Rep Power
    115
    Ah so if I get it right this is what is being suggested. I draw it on paper to understand it better


    making it more time consuming to add or remove nodes, but very fast when looking up. Right?
    what i like about it also is that with the left and right value you can directly see how many child nodes there are by subtracting the left side from the right side minus 1 divided by two.

    May I assume the above update query for pretty much all left and right side values of the children including the super parent is a good practice? (to me it seems so)

    May I also assume that it's wise to add an extra field for the order in which one wants to display the nodes? I think that would be less complex than trying to fit a new child node in between other children instead of as the last child.
    Last edited by aeternus; February 17th, 2013 at 05:02 PM.
  10. #6
  11. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,904
    Rep Power
    1045
    Originally Posted by aeternus
    making it more time consuming to add or remove nodes, but very fast when looking up. Right?
    Exactly. There are also approaches with rational numbers to avoid the expensive updates. But they look rather complicated.



    Originally Posted by aeternus
    May I assume the above update query for pretty much all left and right side values of the children including the super parent is a good practice? (to me it seems so)
    This is the only way to insert a new node (and it's exactly the disadvantage of the whole approach).



    Originally Posted by aeternus
    May I also assume that it's wise to add an extra field for the order in which one wants to display the nodes? I think that would be less complex than trying to fit a new child node in between other children instead of as the last child.
    Not exactly sure what you mean by that.
  12. #7
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    good luck with the nested set model -- if you can work your way through the sql needed for inserts and deletes, you're a better man/woman/coder than many people who have tried it

    if you want a simpler model, especially if you know in advance that there's no way you're going to go deeper than a certain number of levels (your diagram shows only 5 levels, which is very practicable), then check this out --

    Comments on this post

    • Jacques1 disagrees : FUD and a fugly workaround for your beloved toy database system -- great.
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  14. #8
  15. For POny!
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Location
    Amsterdam
    Posts
    416
    Rep Power
    115
    Hi Jacques1,

    Again thanks for your swift and helpful reply! I also looked up the thing with the rational number after your previous post, but that indeed look a bit too advanced although I think the theory behind is to make a node unique by its position and level in the tree. I saw some university paper about it, but I found it a bit too long, for this hour I probably missed the part to calculate back from that unique number.


    Quote:
    Originally Posted by aeternus
    May I also assume that it's wise to add an extra field for the order in which one wants to display the nodes? I think that would be less complex than trying to fit a new child node in between other children instead of as the last child.

    Not exactly sure what you mean by that.
    What I mean is that this technique of adding a new node is pretty doable assuming we add a node to the last position of the tree(level). It can of course happen that in case of a menu we want to change the order of the nodes. So I thought it would be smart to add a field to sort them. I think that is good to do.

    _edit:
    good luck with the nested set model -- if you can work your way through the sql needed for inserts and deletes, you're a better man/woman/coder than many people who have tried it if you want a simpler model, especially if you know in advance that there's no way you're going to go deeper than a certain number of levels (your diagram shows only 5 levels, which is very practicable), then check this out -- Categories and Subcategories
    Hi r937, thanks also for replying, I'll have a look at what you posted, although I thought I got the theory behind the stuff above figured out. (probably not yet the sql for it )

    _edit:2
    Hi R937, yep that looks quite similar to what i proposed at the start , and until today was my only method. I'll give them both a try. Nice tutorial by the way with nice queries already

    Thanks both!!
    Last edited by aeternus; February 17th, 2013 at 06:23 PM. Reason: spelling & added a respons to r937
  16. #9
  17. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,904
    Rep Power
    1045
    No offense, but writing down n joins to get n fixed category levels is a desparate workaround for MySQL (which still doesn't know recursion), not an actual solution.

    Do you even use MySQL? Because if you don't, you could also use a recursive query to retrieve the nodes of your original model in a single query:

    http://explainextended.com/2009/09/2...ts-postgresql/

    On the bottom of the page, there are links to the other database systems, including a (slow) workaround for MySQL.



    Originally Posted by aeternus
    What I mean is that this technique of adding a new node is pretty doable assuming we add a node to the last position of the tree(level). It can of course happen that in case of a menu we want to change the order of the nodes. So I thought it would be smart to add a field to sort them. I think that is good to do.
    Sure, you can do that.
  18. #10
  19. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    Originally Posted by Jacques1
    No offense, but writing down n joins to get n fixed category levels is a desparate workaround for MySQL (which still doesn't know recursion), not an actual solution.
    you are entitled to your opinion

    however, your choice of adjectives reveals an effete elitist arrogance that is very unbecoming

    instead of slamming mysql, go help this guy who is struggling with your nested set model

    Last edited by r937; February 17th, 2013 at 11:31 PM.
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  20. #11
  21. For POny!
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Location
    Amsterdam
    Posts
    416
    Rep Power
    115
    Hi guys,

    thanks for your passionate help I appreciate it
    I do like new and challenging things so glad I learned something new today which took some time. Than again time is relative and sleep is overrated.

    instead of slamming mysql, go help this guys ...l
    Hey that's me . I'm not sure if it was a struggle or just curiosity to optimize my query.

    Do you even use MySQL? Because if you don't, you could also use a recursive query to retrieve the nodes of your original model in a single query
    I am using mysql (innodb), so no recursion for me

    Just to make sure. I am going to make a task manager with an unknown amount of categories. This list will probably be shown in its entirety (than maybe some user preferences will be stored as time passes). The user can add categories
  22. #12
  23. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,777
    Rep Power
    348
    Originally Posted by Jacques1
    However, it has one big disadvantage: You need multiple queries (one for each level of the tree) to retrieve the subcategories for a given category. This is cumbersome and slow.
    No you don't. Modern databases can efficiently query that using recursive queries.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  24. #13
  25. For POny!
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Location
    Amsterdam
    Posts
    416
    Rep Power
    115
    Quote:
    Originally Posted by Jacques1
    However, it has one big disadvantage: You need multiple queries (one for each level of the tree) to retrieve the subcategories for a given category. This is cumbersome and slow.
    Originally Posted by shamat
    No you don't. Modern databases can efficiently query that using recursive queries.
    Thanks for your reply shammat, does this apply to mysql? (that's what i am working with). Could you give an example in that case?
  26. #14
  27. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,777
    Rep Power
    348
    Originally Posted by aeternus
    Thanks for your reply shammat, does this apply to mysql? (that's what i am working with).
    No, I was talking about modern databases
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  28. #15
  29. For POny!
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Location
    Amsterdam
    Posts
    416
    Rep Power
    115
    Originally Posted by shammat
    No, I was talking about modern databases
    too bad I can't use those other databases. Thanks for replying!
Page 1 of 2 12 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo