SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Enthusiast
    Join Date
    Jul 2006
    Posts
    90
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Searching a tree structur with pointers?

    Hi,

    I work with a complicated system which mainly contains users in a tree structure. The particular is that I have created some pointers to avoid having double set with users and it works just fine. My issue the search, how can I search Users from the db table?

    Example

    Users
    |
    |----- User 1 (id = 1, type = user, parent = 0)
    |---------- User2 (id = 2, type = user, parent = 1)
    |---------- User3 (id = 3, type = user, parent = 1)
    |--------------- User4 (id = 4, type = user, parent = 3)
    |----- User 5 (id = 5, type = user, parent = 0)
    |---------- User3 (id = 100, type = pointer, parent = 5)
    |---------- User6 (id = 6, type = user, parent = 5)
    |--------------- User4 (id = 101, type = pointer, parent = 6)

    Values
    Id = 1, propertyId = 1, UserID = 1, value = user1
    Id = 2, propertyId = 2, UserId = 1, value = User1@email.com
    Id = 1, propertyId = 1, UserId = 2, value = user2
    Id = 2, propertyId = 2, UserId = 2, value = User2@email.com
    ...

    Properties
    Id = 1, name = Name
    Id = 2, name = Email
    ....

    What I want is to search only the users under User5, one possibility is to go through all the users who are under it, and compare with the search option but it is not what makes the most sense, I believe when we are talking about tens of thousands of users, it will take too long time.

    So is there some other way to do it? Should I design the system differently?

    Thanks in advance
    Regards
    OXODesign

  2. #2
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Is user 4 having two different parents a typo?

    Have you seen this method?
    http://www.sitepoint.com/article/hie...ta-database/2/
    It should be very fast for finding which users have a certain parent or ancestor.

  3. #3
    SitePoint Enthusiast
    Join Date
    Jul 2006
    Posts
    90
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It was a typo, sorry.

    The method used there, is not relevant in my case since we are talking about tens of thousands of users, so if I should add a user in the start I must updatere all users after it!

  4. #4
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    How deep can these hierarchies get? Are there any solid characteristics to this, such as a maximum number of levels, or maximum number of user at each level? I take it you add users frequently? Details...

  5. #5
    SitePoint Enthusiast
    Join Date
    Jul 2006
    Posts
    90
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by crmalibu View Post
    How deep can these hierarchies get? Are there any solid characteristics to this, such as a maximum number of levels, or maximum number of user at each level? I take it you add users frequently? Details...
    It is unlimited but I will think that it will reach at max 10 levels deep! But the of users for each lever will be from 100-1000.

  6. #6
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You could use this method
    http://sqllessons.com/categories.html

    But it requires you supply enough joins to handle the maximum depth that may exist. I think you could also use a stored procedure to handle arbitrary depth.

  7. #7
    SitePoint Wizard silver trophy kyberfabrikken's Avatar
    Join Date
    Jun 2004
    Location
    Copenhagen, Denmark
    Posts
    6,157
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I would suggest either a path enumeration or a closure table for this. See this slide for details.


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
  •