Re: Figuring out relationships between tables. - Mailing list pgsql-general

On Sun, Aug 23, 2009 at 09:47:06AM -0500, Andrew Cooper wrote:
> Greetings,
>
> This is a general database design question.  I've got a database where I
> need to hold information on employees.  Every employee has much of the
> same information so I've created an Employee table.  Now, some employees
> are supervisors or managers.  An employee can only have 1
> manager/supervisor but the hierarchy can be varying depths.  For
> example...
>
> I have the following employees:
>
> Bob, Jill, Tom, Bill, Harry, Jane, Amy, Jim, Fred, Sue and June
>
> The management hierarchy works like this:
>
>              Bob
>         /      |    \
>     Jill      Tom   Bill
>     /  \       |      \
> Harry  Jane   Amy     Jim
>   |                    |
>  Fred                 Sue
>   |
>  June
>
> How can I create these relationships in the database?  I don't want a
> Manager table that duplicates all the information for employees for the
> managers.  Besides, a manager can have a manager who can have a
> manager... and so on.  There is probably a simple solution to this
> problem but I'm not seeing it.  Any help would be appreciated.
>
> Thanks,
>
> Andrew

Before 8.4, you had to do some awful hacks.  With 8.4, you use Common
Table Expressions
<http://www.postgresql.org/docs/current/static/queries-with.html> to
do something like this:

CREATE TABLE employee (
    employee_id INTEGER PRIMARY KEY, /* You'll an actual uniqueness
                                        constraint besides this, but
                                        that's for another time. */
    first_name  TEXT NOT NULL,
    last_name   TEXT NOT NULL,
    ....
);

CREATE TABLE organization (
    employee_id INTEGER NOT NULL
                REFERENCES employee(employee_id),
    boss_id     INTEGER /* The "root" of this tree has no boss. */
                REFERENCES employee(employee_id),
    CHECK(employee_id <> boss_id)
);

Now you have your table of organization separate from the employee
table.  There are several ways to ensure that this is in fact a tree,
but let's assume you're handling this manually.

Now, when you want an org chart, you can do:

WITH RECURSIVE t AS (
    SELECT e.employee_id, e.first, e.last, ARRAY[e.employee_id] AS "path"
    FROM employee AS e
    JOIN organization AS o ON (
        e.employee_id = o.employee_id AND
        o.boss_id IS NULL
    )
UNION ALL
    SELECT e.employee_id, e.first, e.last, t.path || e.employee_id AS "path"
    FROM employee AS e
    JOIN t ON (
        e.boss_id = t.employee_id AND
        e.employee_id <> ANY(t."path") /* Prevent loops */
    )
)
SELECT
    REPEAT('--', array_upper(t."path")-1) || employee_id as employee_id,
    t.first || ' ' || t.last AS "Name"
FROM t;

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate