ansaurus

Question

Answer 1

A:

You could get a list of all of the employees a manager manages by doing this on your tabs:

SELECT * FROM employees WHERE `superior`='id goes here'

But if you or bout making more than one superior just make a new table with columns like this

superior, person

or if you wanted to show it like a tree just do it in a loop of queries

kennyisaheadbanger 2009-09-20 19:10:29

Sorry I forgot to mention that one employee can have at most 1 superior, so its more important to have hierarchy from top until bottom like general manager can view data of all employees. Unlike shift manager who is able to view data of all employees but not able to view data of other shift managers.

c0mrade 2009-09-20 19:15:56

well that's easy because then you just do like the first query above

kennyisaheadbanger 2009-09-21 17:05:42

Answer 2

+1 A:

Well, with a simple adjacency graph (supervisor_id pointing back at the employees table), you could certainly do what you want, but it won't be very efficient, and will not scale to large numbers of people.

What you probably want is to implement a nested set model. This allows you to very easily grab everyone who reports to some arbitrary person in the organization.

If you're early enough in development, you might consider looking at the Doctrine ORM system, which provides a nestedset behavior for models, so you don't need to implement your own.

Edit: Richard Knop has a post about nested sets with php example code which you might find more helpful than Celko's 100%-sql examples..

timdev 2009-09-20 19:12:49

I like your answer, now I'm going to do some reading .. and hopefully the things will be how they are supposed to be :D tnx

c0mrade 2009-09-20 19:30:52

I have question concerning this article http://www.intelligententerprise.com/001020/celko.jhtml;jsessionid=CQYCHUUILHLFRQE1GHRSKH4ATMY32JVN how to insert data into database , by inserting them manually creating PHP form to do it like with calculating these : This has some predictable results that we can use for building queries. The root is always of the form (left = 1, right = 2 * (SELECT COUNT(*) FROM TreeTable)); leaf nodes always have (left + 1 = right); the BETWEEN predicate defines the subtrees; and so on.

c0mrade 2009-09-20 20:09:08

Not sure exactly what you're asking, but hopefully the link I just added to my answer will help elucidate things for you.

timdev 2009-09-20 20:20:50

I wouldn't tout nested sets as a global panacea. Adjacency model do actually scale well. A binary tree is essentially an adjacency model, and I don't hear people bemoaning those. Even Indexes are normally implemented as trees / adjancency models. Nested sets are often worse at updates, and incredibly poor at searching up a tree instead of down (who is my superior is hard in nested sets). I would go as far as saying adjacency is the way to go unless you can articulate a reason that nested sets are needed for performance tuning.

Dems 2009-09-20 20:37:23

Thank you :D exactly what I wanted

c0mrade 2009-09-20 20:42:39

If you want to use an adjacency model with a table linking employees and supervisors then I advise putting queries that need many selects into the link table into a stored procedure on the SQL server, because it's a query per link otherwise. I always try for one DB query, work in big chunks of data and never ever ever put a DB query in a loop. Latency hurts.

Zan Lynx 2009-09-21 02:27:36

@Dems There's nothing wrong with an adjacency graph in a lot of situations. I certainly didn't mean to make nested sets come off as a panacea. However, when you're dealing with the sort of domain that the OP is talking about (an org chart, basically), you're going to be a lot more reading than writing. I haven't thought about the "who's your daddy" problem recently, but I seem to remember one fix being storing the depth of each node as well. Then it's just a matter of finding a node with depth=mine-1, left<my_left, right>my_right, no?

timdev 2009-09-21 02:58:29

Answer 3

A:

Maybe you would be better off creating an additional table or list with roles / functions and assigning levels to each function, so a level 4 would be the direct boss of a level 3 etc.

jeroen 2009-09-20 19:15:16

Answer 4

A:

I suggest a nested set model. However said it will only allow 1 person to have 1 manager directly above them...

I wrote a blog on this a couple of years ago. OK its in MSSQL but it should convert to MySQL ok.

Link here

This shows how to insert/move/retrieve full lists etc.

Rippo 2009-09-20 19:18:29

Answer 5

A:

You can do this with three fields, an "ID" and a "managed IDs" range (two values). These IDs are not based in anything real, they are simply to describe the hierarchy.

Then to find all the managed employees you select every ID in the manager's "managed IDs" range. The top managers ID range covers everybody, the managers under him cover a smaller range, etc, down to the people who don't manage anybody and have a range containing just themselves.

One problem with this design is that when you shuffle people around or add new people you sometimes have to renumber everybody in order to keep the hierarchy straight. If you use nice big spacing of IDs for this then you don't have to renumber the entire thing too often. Or you can use floating point ID fields.

Edit: After reading other responses I notice that this is the nested set model, or a variant of it.

Zan Lynx 2009-09-20 19:21:53

Answer 6

A:

If you are dealing with shifts, then the shift manager will vary separately from the staff working on the shift. That is, this week, a chef might be working the lunch-time shift under one shift manager; next week, he might be working the evening shift under another manager. Be sure you keep such complexities in mind.

Jonathan Leffler 2009-09-20 20:16:21

ansaurus

tags:

views:

answers:

Database design question

related questions