SitePoint Sponsor

User Tag List

Results 1 to 15 of 15
  1. #1
    SitePoint Addict D3V4's Avatar
    Join Date
    May 2010
    Posts
    370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    I need help with the structure of a database

    I'm sorry for the title of the topic but I really didn't know how to summarize my problem in a single line

    I'm building a website for a company with which the users are going to insert professional appointments. All the data about the appointments will be stored in a database table, so that the users can come back and look at all the information they have entered.

    The managers of the company will also have to be able to look at all the information entered by the users, but not by all of them. This is a fast drawing that I've done to show you an example of the hierarchy of the company:



    So, for example, User 3 will see data about User 1 and User 2, but not about User 5.

    User 5 will see data about User 6, 7, 8, 9 and 10.

    User 11 will see data about everyone.

    I hope this is pretty clear, I wanted to ask you what's the best way to implement this into a database.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    it's a classic hierarchical structure... this should help: http://articles.sitepoint.com/articl...-data-database
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict D3V4's Avatar
    Join Date
    May 2010
    Posts
    370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    it's a classic hierarchical structure... this should help: http://articles.sitepoint.com/articl...-data-database
    Very useful, thank you

  4. #4
    Non-Member Kalon's Avatar
    Join Date
    Aug 2010
    Location
    At my computer
    Posts
    2,012
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by D3V4 View Post
    I wanted to ask you what's the best way to implement this into a database.
    My suggestion is to use the Nested Set Model.

    Once you get your head around how the left and right values are used to store hierarchical information, life should become much easier - well it did for me at least.

    if you need additional sql scripts to move nodes and all their children, I have posted some in this thread

  5. #5
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,192
    Mentioned
    17 Post(s)
    Tagged
    4 Thread(s)
    More people will understand an adjacency list, thus it will be easier to maintain. Not to mention a single node out of place won't explode the entire thing and meaning can be made of it when browsing the table in SQL for DB admins. A nested set might as well be an alien language when looking at the SQL table. Nested sets are like glass imo – so long as you rarely touch them they are fine. However, one person slightly modifies the code due to some type of misunderstanding and boom goes the entire thing. If you are working with other people nested set will most likely result in more problems than it will solve, that some intelligent caching can't resolve.

    None the less, I would probably recommend using the titles/roles within the company as a hierarchy. It seems like parent roles (superiors) have access rights to children (employees) , so that would make sense.

  6. #6
    Non-Member Kalon's Avatar
    Join Date
    Aug 2010
    Location
    At my computer
    Posts
    2,012
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by oddz View Post
    More people will understand an adjacency list, thus it will be easier to maintain.
    not sure if more people will understand the adjacency list model, but it doesn't really matter because you'll probably find that many understand both.

    The nested model is not rocket science (or even brain surgery) and imho is easier to maintain.

  7. #7
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,192
    Mentioned
    17 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by oddz
    The nested model is not rocket science
    You might be surprised.

  8. #8
    Non-Member Kalon's Avatar
    Join Date
    Aug 2010
    Location
    At my computer
    Posts
    2,012
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    obviously for some it could be, depending on one's aptitude for this type of thing.

    but once I got my head around that the left and right values are used solely to store the hierarchal structure and to move/delete nodes and their children involves just manipulating the left and right values life became so much easier for me.

    I even posted some scripts that move nodes and their children in this thread, for any one interested, because I couldn't find what I needed on the www.

    if people choose to use the adjacency list model I have no issue with that, but I will always recommend the nested set model.

    now I have written my own php class which manages categories in a database using the nested set model.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Kalon View Post
    ...you'll probably find that many understand both. .
    until you showed up, we routinely had nested set questions posted in this forum go unanswered
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    Non-Member Kalon's Avatar
    Join Date
    Aug 2010
    Location
    At my computer
    Posts
    2,012
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    until you showed up, we routinely had nested set questions posted in this forum go unanswered
    so what point are you trying to make?

    what you say may or may not be true but in either case I don't see that it proves anything.

    could it be they should have gone to another website?

  11. #11
    Non-Member Kalon's Avatar
    Join Date
    Aug 2010
    Location
    At my computer
    Posts
    2,012
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    for anyone interested or puzzled about the nested model,

    from this tutorial

    .....the concept of nested sets in SQL has been around for over a decade, and there is a lot of additional information available in books and on the Internet. In my opinion the most comprehensive source of information on managing hierarchical information is a book called Joe Celko's Trees and Hierarchies in SQL for Smarties, written by a very respected author in the field of advanced SQL, Joe Celko. Joe Celko is often credited with the nested sets model and is by far the most prolific author on the subject.....

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Kalon View Post
    so what point are you trying to make?
    you said "many understand both" and of course you are entitled to your opinion

    i simply gave my impression based on my experience in this forum that it is actually few, not many

    i'm suprised you failed to see my point, kalon

    i wasn't trying to "prove" anything and i wish you would stop being so defensive

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

  13. #13
    Non-Member Kalon's Avatar
    Join Date
    Aug 2010
    Location
    At my computer
    Posts
    2,012
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    you said "many understand both" and of course you are entitled to your opinion

    i simply gave my impression based on my experience in this forum that it is actually few, not many

    i'm suprised you failed to see my point, kalon

    i wasn't trying to "prove" anything and i wish you would stop being so defensive

    if you're going to quote me, you should quote the whole sentence and not take the 3 words out of context that suit whatever agenda you are pushing.

    what I actually said was

    not sure if more people will understand the adjacency list model, but it doesn't really matter because you'll probably find that many understand both.
    that doesn't mean you will find many on all accoassions.

    how many you actually find will depend on the where the sample of people is taken and the size of the sample.

    it could be many or it could be few.

    I suppose that could then suggest that many of the enquiries about the nested set model over at webdeveloper.com were from sitepoint refugees

  14. #14
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Kalon View Post
    I suppose that could then suggest that many of the enquiries about the nested set model over at webdeveloper.com were from sitepoint refugees
    maybe that's where ~all~ nested set people should go

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

  15. #15
    Non-Member Kalon's Avatar
    Join Date
    Aug 2010
    Location
    At my computer
    Posts
    2,012
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    if they can't get the help they need from here, then that is one option they have


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
  •