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.
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
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.
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.
…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 [I]Joe Celko’s Trees and Hierarchies in SQL for Smarties[/I], 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…
