SitePoint Sponsor

User Tag List

Results 1 to 19 of 19
  1. #1
    Knowledge is key 2 progression Tryst's Avatar
    Join Date
    Sep 2003
    Location
    Wales
    Posts
    1,181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Best database design option???

    Hi all,

    I am going to start designing/creating a database that will contain details of products. These products will then be the child of categories, who will then also be childs of other categories etc. In all the database will consist of 5 levels (parent-child) wise.

    As this is my first project in creating a database like this, I would appreciate it if someone could tell me what the best way of creating a database with this type of data would be.

    Should I have 2 tables (products, categories) with the categories table having a parent field to state what its a child of??

    Tryst

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    yes, just like that, a products table and a categories table which includes a parentid

    see http://tinyurl.com/rg6e for some category queries (registration required, but it's free)
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Knowledge is key 2 progression Tryst's Avatar
    Join Date
    Sep 2003
    Location
    Wales
    Posts
    1,181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    But if I was to a products that are to be within many categories, would then need to another table that comprises of the primary key from both, the products table and the categories table to elimimate the many-to-many relationship.

    Now i'll end up with 3 tables and 2 one-to-many relationships. Is this right??

    Cheers

    Tryst

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    that is exactly right

    you sure you haven't done this before? if you're just guessing, may i suggest you buy a lottery ticket tonight...

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Knowledge is key 2 progression Tryst's Avatar
    Join Date
    Sep 2003
    Location
    Wales
    Posts
    1,181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Let me post me message again. This time in English

    But if I was to have some products (within the products table) that can be within (child) many categories , would I then need to add another table that comprises of the primary key from both, the products table and the categories table to elimimate the many-to-many relationship.

    Now i'll end up with 3 tables and 2 one-to-many relationships. Is this right??

    Cheers

    Tryst

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    yes, that is exactly right

  7. #7
    Knowledge is key 2 progression Tryst's Avatar
    Join Date
    Sep 2003
    Location
    Wales
    Posts
    1,181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    lol!!

    I've done a few tutorials/articles and am just starting to get my head around it I just needed some concrete suggestions off you knowledgable guys.

    Now I need some suggestions on what are the best ways of extracting and displaying hierarchical data on a web page. Adjacent lists, modified pre-order tree traversal??? Any opinions??

    Cheers

    Tryst

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by Tryst
    Now I need some suggestions on what are the best ways of extracting and displaying hierarchical data on a web page. Adjacent lists, modified pre-order tree traversal??? Any opinions?
    adjacency list model

    plus, a healthy dose of IA (information architecture) as applied to the UI (user interface)

    take the DMOZ/Yahoo directory for a shining example

    does it explode the tree all the way down?

    nope, you only get to see one level at a time

    now, one level at a time is perhaps a bit too restricting, so let's say you show the tree no more than three levels down

    you can write a very nice, fairly efficient, four-way left outer self-join to handle that

    and everybody else that comes after you will be able to understand and maintain your code

    the modified pre-order thingie, as explained in the sitepoint article, is too weird and complex for me -- if you really dislike the adjacency list model, i would suggest skipping the modified pre-order thingie and going straight to the nested set model

    the nested set model, however, suffers from a similar problem as the modified pre-order thingie: it takes a joe celko or nagrom to understand it


  9. #9
    Knowledge is key 2 progression Tryst's Avatar
    Join Date
    Sep 2003
    Location
    Wales
    Posts
    1,181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I understand the Modified Pre-order Tree Travesal slightly, but I find it confusing when you need to add products to the database, U need to shift each products left and right values up by 2 depending on where exactly the product will be entered into the database.

    When you say I can 'write a very nice, fairly efficient, four-way left outer self-join to handle that', do you mean a query? If so, would I not then need any of the algorithms I mentioned earlier?

    Cheers

    Tryst

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    yes, a query -- a four-way left outer self join will show current plus 3 levels of the tree

    inserts, updates, and deletes, of course, are extremely simple, right?

    what algorithms did you think you need?

  11. #11
    Knowledge is key 2 progression Tryst's Avatar
    Join Date
    Sep 2003
    Location
    Wales
    Posts
    1,181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have an algorithm (or piece of PHP code, even) that displays all relevant levels of a suggested product, using the Adjacent list theory, but its quite inefficent as it uses multiple queries.

    U don't have any sample queries I could use/learn that would produce what U have mentioned do you?

    Inserting/deleting, and updating a database is easy yeah, but its what needs to be done afteradding a product with the Modified Pre-Order Tree Travesal that I don't like, thats why I prefer using the Adjacent Model.

    Tryst

  12. #12
    Knowledge is key 2 progression Tryst's Avatar
    Join Date
    Sep 2003
    Location
    Wales
    Posts
    1,181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Uhm, r937, did U get my last reply??

    Cheers

    Tryst

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    d*mn! i'm sure i answered this, but it was at work, and i don't always sit there and wait for the post to go through, sometimes it takes a while, and maybe i closed the browser too soon...

    ah well, thanks for the reminder, it's relatively easy to write this type of query..
    Code:
    select L1.name as L1name
         , L2.name as L2name
         , L3.name as L3name
         , L4.name as L4name     
         , L5.name as L5name     
      from categories L1
    left outer
      join categories L2
        on L1.id = L2.parentid      
    left outer
      join categories L3
        on L2.id = L3.parentid      
    left outer
      join categories L4
        on L3.id = L4.parentid      
    left outer
      join categories L5
        on L4.id = L5.parentid      
     where L1.parentid is null
    note the query starts at the root(s) of the tree(s) by selecting, in the WHERE clause, only those nodes which have no parent

    then because the joins are LEFT OUTER, the hierarchy is built up, to a maximum level of 5 deep

    in most applications of hierarchies, there is a maximum number of levels, so this sort of approach works, and recursion is not really required

    rudy

  14. #14
    SitePoint Wizard Busch's Avatar
    Join Date
    Jan 2004
    Posts
    1,072
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    Code:
    select L1.name as L1name
         , L2.name as L2name
         , L3.name as L3name
         , L4.name as L4name     
         , L5.name as L5name     
      from categories L1
    left outer
      join categories L2
        on L1.id = L2.parentid      
    left outer
      join categories L3
        on L2.id = L3.parentid      
    left outer
      join categories L4
        on L3.id = L4.parentid      
    left outer
      join categories L5
        on L4.id = L5.parentid      
     where L1.parentid is null
    Sorry to dig up such an old thread but my question is relevant to this specific query.

    I want to use this query for a category navigation menu. I want all of the categories to be listed no matter what category the user is in or how many levels deep the category is.

    I've got this working almost perfectly but I'm having 1 problem. If a parent category has more than 1 sub-category, the parent category is listed twice. I've tried using DISTINCT and GROUP BY in the query but still no luck (query and results posted below).

    How can I get non-repeating results when using this query?

    Thanks,

    This query produced the list below:
    Code MySQL:
    SELECT DISTINCT
    	L1.category_name AS L1name, 
    	L2.category_name AS L2name, 
    	L3.category_name AS L3name, 
    	L4.category_name AS L4name, 
    	L5.category_name AS L5name
    FROM 
    	default_categories L1
    LEFT OUTER JOIN default_categories L2 ON L1.category_id = L2.category_parent      
    LEFT OUTER JOIN default_categories L3 ON L2.category_id = L3.category_parent      
    LEFT OUTER JOIN default_categories L4 ON L3.category_id = L4.category_parent      
    LEFT OUTER JOIN default_categories L5 ON L4.category_id = L5.category_parent      
    WHERE L1.category_parent = ''

    • Dogs
    • Cats
    • Bags
      • Gear Bags

    • Bags
      • Handlebar Bags

    • Bicycle Lights
      • Headlights

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    when you think about what a query's results look like when a query produces results -- i.e. a "result table" consisting of rows and columns -- then you'll realize that of course the parent will value "repeat" on multiple rows if it has multiple children

    you would "suppress" this in your application layer, not with sql
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #16
    SitePoint Wizard Busch's Avatar
    Join Date
    Jan 2004
    Posts
    1,072
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok, that helps me eliminate the query as a problem. thanks.

  17. #17
    SitePoint Wizard Busch's Avatar
    Join Date
    Jan 2004
    Posts
    1,072
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    when you think about what a query's results look like when a query produces results -- i.e. a "result table" consisting of rows and columns -- then you'll realize that of course the parent will value "repeat" on multiple rows if it has multiple children

    you would "suppress" this in your application layer, not with sql
    hi,

    i just noticed you have exactly what I want to do here under Displaying all categories and subcategories: site maps and navigation bars: http://sqllessons.com/categories.html

    was that all done through your db query or did you use a programming language to alter the output?
    Last edited by Busch; Aug 22, 2007 at 16:52.

  18. #18
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by sqllessons.com/categories.html
    What's the easiest way to transform the result set into the nested ULs? In ColdFusion, we use nested CFOUTPUT tags, with the GROUP= parameter on all but the innermost list. Very straightforward indeed. In other scripting languages, as the saying goes, your mileage may vary.
    sorry, i don't do php
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  19. #19
    SitePoint Wizard Busch's Avatar
    Join Date
    Jan 2004
    Posts
    1,072
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    sorry, i don't do php
    fair enough. i thought that might be the case. i think i have an alternative worked out for now. we'll see...

    thanks for providing the resource!

    eric


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
  •