Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Feb 2004
    Posts
    6

    Question Unanswered: Celko nested sets vs. linked tables

    Hi,

    I am trying to revamp our product database with a view to making it search-optmised and would like some guidance (or confirmation of method, if you will!!). We currently use a three table structure (Product, Brand, Cat(egory)) along the lines of :

    create table product
    (prod_id int not null,
    brand_id int not null,
    cat_id int not null,
    other stuff e.g. tech. specs, displayed text on web page, etc....
    )

    with corresponding brand_id and cat_id in the other tables. While this seems relationally sound I see it as being inefficient for searching, particularly after reading the theory behind nested sets.

    A new function I am building will enable users to drill down through the product list or runs searches against all or part of the db :

    e.g. all products from one category,
    all products fitting certain search criteria,
    products from several selected brands fitting certain criteria,
    and any combination of the above you can think of!

    The problem is, not all products have the same criteria list (in fact I would be surprised if any did) and may also be of more than one category (a digital camera with movie mode might easily fit into the digital camcorder search). I think I am correct in that a nested set would make the structure fit the requirement - things like criteria, displayable text, etc. could be nodes in their own right and each logical level might have its own criteria. For example, if a category is selected then certain text must be displayed and could list further categories or products. The next level down would then require its own displayable text - I am mainly thinking about SEO tags here. Also, I am not precious about retaining the current table structure and would like an open ended solution where I can add further data/functionality in a dynamic fashion, which nested sets seem to embody.

    Does this make sense to anybody coz I think I've confused myself even more!!

    Thanks in advance,

    G

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    ok....a collective ...huh?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002

    Re: Celko nested sets vs. linked tables

    Originally posted by hoyleg
    Does this make sense to anybody coz I think I've confused myself even more!!
    <voice type="curly">
    I'm tryna think but nothing happens!
    </voice>
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Feb 2004
    Posts
    6

    Talking hmm collective...

    hehe ok my sides are hurting from the 3-stooge-fest but I still don't feel I'm much closer to an answer ;-)

    Collectives? That sounds like a reasonable term but not one I've ever read about or heard mentioned (but then my db training is around 10 yrs old). Care to elaborate (I wonder if it's an american term for something I've previously heard under a dif. name)?

    Cheers,

    G

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, let's start with your requirement to "drill down through" the products

    this implies a bill of materials structure, product components within products within product assemblies, right?

    do a search for adjacency model, you will get plenty of hits in this site here
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2004
    Posts
    6

    Arrow adjacency

    It's not BoM I'm trying to achieve here, more a dynamic category, subcategory relationship (where subcategory may or may not exist) and I've a good idea how to achieve this with sets. The slightly more complicated problem is how to enable search by brand - if product is the last node in the tree (say) and brand is specific to product then a query to determine which categories to display given a specific brand becomes quite hairy. I guess I'm trying to combine two ways of looking at my data - bottom-up and top-down in one method.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    definitely if category belongs to brand then it's bottom up

    and what do you do if a category at a higher level belongs to a different brand than categories at lower levels in its subtree?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Re: adjacency

    Originally posted by hoyleg
    It's not BoM I'm trying to achieve here, more a dynamic category, subcategory relationship (where subcategory may or may not exist) and I've a good idea how to achieve this with sets. The slightly more complicated problem is how to enable search by brand - if product is the last node in the tree (say) and brand is specific to product then a query to determine which categories to display given a specific brand becomes quite hairy. I guess I'm trying to combine two ways of looking at my data - bottom-up and top-down in one method.
    Exactly....Bottom Up...And it sounds like the data is fairly static no?

    It's like code tables...

    Have a nightl process to rebuild to data. Have the element column 1, column 2 the level, column 3 the lineage of the data...then you'll have it all in one reference....

    you can even have the grandady of all in one seoarate column...

    granted this will take iterations and a cursor, but it'll be done overinight...

    oh, and collectivley....meant speaking on behalf of (which I have no right to do) the entire forum...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Oh and hey....check this out...

    http://www.sqlteam.com/item.asp?ItemID=8866
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that robvolk method (depth and lineage) is but one way to skin the cat

    here's another: Multi-Threaded Message Board Solution -- Hierarchical Data without the Adjacency Model
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Very cool...but rank is already associated with the data....without it, won't it be a problem?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  13. #13
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    May be a tad out of my depth here, so feel free to give me change for my .02 USD.

    I don't see rank as having anything to do with the data. The data could be ordered in any number of ways (price, number of buttons, shininess of casing material, etc.). Because of that, I am not sure a Hierarchical model is going to be so good for the problem at hand. Would it be better to just have a mapping table between the category table and the product table, so you can have different products be members of multiple different categories (as in the digital camcorder example above)?

    Not too sure how you mean drill down. Do you mean from category to product? Or are the products grouped in other ways?

  14. #14
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by MCrowley
    May be a tad out of my depth here, so feel free to give me change for my .02 USD.

    I don't see rank as having anything to do with the data. The data could be ordered in any number of ways (price, number of buttons, shininess of casing material, etc.). Because of that, I am not sure a Hierarchical model is going to be so good for the problem at hand. Would it be better to just have a mapping table between the category table and the product table, so you can have different products be members of multiple different categories (as in the digital camcorder example above)?

    Not too sure how you mean drill down. Do you mean from category to product? Or are the products grouped in other ways?
    No I think you're correct....it's just that I still don't understand....

    What's the goal here?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  15. #15
    Join Date
    Feb 2004
    Posts
    6

    Smile

    ah! a collective ... d'oh!

    some good input here - I'm just applying my model to those suggested to work out which is best. my brain really hurts!

    I'll try and fill in a few gaps too -

    Yes, the data could simply be described in a couple of look-ups but I'm trying to optimise for selects. It might not be a problem initially but when I'm fielding 100k+ records against ~100 brands and ~100 categories (some with subcats) I am worried response times will be poor, especially when I'm specifiying lots of 'where' clauses on top of two joins (+ non-dedicated server). What initially attracted me to nested sets was the fact that because you already know the first and last id for every child/grandchild of a node a select is a one-liner and wheres can be piled on afterward without much worry.

    That and the products do appear to be grouped in two ways - by category and by brand. I see category as being the most important as users are more likely to be comparing products of the same type, which would mean brand could then simply be a criteria to search under. OK, I think that's one question answered!

    To recap I see a table (nested set) of categories (an example traversal would be: Digital Products -> DVD Equipment -> DVD Recorders -> Products, another: Digital Products -> Digital Cameras -> Products). Everything on a last node is then a product, all others are category and will be used for most display purposes (breadcrumb trails, etc.). Every category/subcategory/product would have displayed text as additional fields (h1, h2, meta tags, etc.) and only product would have manufacturer as an ID against a second, look-up table (to avoid any major data redundancy). Given this setup, where are criteria lists best placed? This would essentially be a list of tech. features a user could specify to narrow their search down a bit and would change with every category/subcategory/product.

    Have a look at www.dealtime.co.uk as a nice indication of where I am trying to go with this, only with an additional emphasis on a really powerful search facility to complement. Drill down through the categories and have a look what pops out - products and subcats on the same page, etc. I don't suppose anybody reading this designed this site?? No? Bugger...

Posting Permissions

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