Re: Figuring out relationships between tables.

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(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

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