Best practice for storing tree data in a database

Question

I think the classic form of the problem is you have employees and employees have managers so the manager has a manager unless he's the CEO.

So how do you store the data? Solution 1: It would seem that the employee table can have manager_id or you can have an employee_manager table (that way you can have many or zero managers).

Some people say solution 1 is a bad idea because SQL doesn't support recursion and there is no query to find all managers above an employee. These people have alternative ideas (like the employee has a list of managers) however they all seem to involve a mess of unnormalized data that is extremely hard to maintain.

So what do you all think?

Answer 1

i agree with others.. recursion is available.

I would not place the manager_id in the emp table (despite the SCOTT/TIGER ancient wisdom). The real world breaks this business rule all the time, and it is not well normalized.

instead think of a person_to_person type link table, where two people are related to each other during a time period with a role... for example person1 is related to person2 from January through March as a manager. this allows you great flexibility in assigning people to projects, departments, arbitrary groups through time, even with the reality of having multiple managers at some points in time.

also consider that people to department relationships are similar - people may be related in subtle ways to multiple departments at the same time.

Answer 2

Most popular SQL databases do support recursive queries.

• Most databases support recursive CTEs.
• Oracle also supports CONNECT BY.
• MySQL doesn't support either, unfortunately.

Answer 3

Some databases do have recursive queries, like the CONNECT BY construct in Oracle. In that case, I would certainly opt for 1.

But even if not, I think the data is cleaner if you give everyone his own manager, if that is indeed the structure of the data. You need to run multiple queries to get all the managers up to the CEO, or you must get all data and build the tree in whichever programming language you're using. But you got the same problem if every person gets a list of managers. You will need some programming intelligence if you want to build a tree out of this data. Unless you got Oracle, that is. ;)

By the way, I would opt to make a list of departments, and give each department a manager. That way, you can more easily put persons (even managers) in a different position, without having to update employees. Usually managers manage a department, so they're only other people's managers, because those people work in that department.

Answer 4

You have to decide where to enforce your data's integrity constraints, and where to query it/do interesting things with it.

As others have mentioned, some database servers do not support recursive querying; however, if you're going to query the database, then build a tree in code -- then this is a moot point.

But even if you can do that in SQL -- do you really want to? SQL is great for some things, but not for all things.

The primary concern should be to let the database do what it's good at -- which in this case, sounds like Solution 1.