pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedI'm stumped
3+ messages / 3 participants
[nested] [flat]
* I'm stumped
@ 2004-12-20 16:30 Doug Y <dylists@ptd.net>
2004-12-21 22:39 ` Re: I'm stumped thomas.silvi <thomas.silvi@laposte.net>
0 siblings, 1 reply; 3+ messages in thread
From: Doug Y @ 2004-12-20 16:30 UTC (permalink / raw)
To: pgsql-sql
I can't figure out an efficient way to do this. Basically I had a typical 3-tier relationship:
(Employee -> Department -> Division)
However, at some point the need to move employees arose, but instead of changing the key in the emp table, we now have
an over-ride table, so a history can be tracked.
If I want to get the info for a particular employee, its a pretty simple deal, however, getting all the employees for a
dept or division has become troublesome.
A very simplified schema:
divisions ( div_id, div_name );
departments ( dept_id, dept_name, div_id );
employees ( emp_id, emp_name, dept_id );
emp_dept ( emp_id, dept_id, active, changed_by, changed_when );
The original way that worked well:
SELECT v.div_name, d.dept_id, e.emp_id, e.emp_name
FROM divisions v
INNER JOIN departments d
ON d.div_id = v.div_id
INNER JOIN employees e
ON e.dept_id = d.dept_id
WHERE v.div_id = 123;
What was initially tried:
SELECT v.div_name, COALESCE(ed.dept_id, d.dept_id), e.emp_id, e.emp_name
FROM divisions v
INNER JOIN departments d
ON d.div_id = v.div_id
INNER JOIN employees e
ON e.dept_id = d.dept_id
LEFT JOIN emp_dept ed
ON ed.emp_id = e.emp_id AND ed.active = true
WHERE v.div_id = 123;
This query is flawed, as it still always puts the employees in their original div, but reports the new dept. Which we
didn't catch as a problem until emps were moved to depts in a different division.
I tried creating a function:
CREATE OR REPLACE FUNCTION get_empdept(int4) RETURNS int4 AS '
SELECT CASE WHEN ed.dept_id IS NOT NULL
THEN ed.dept_id
ELSE e.dept_id END
FROM employees AS e
LEFT JOIN emp_dept AS ed
ON ed.emp_id = e.emp_id AND ed.active = true
WHERE e.emp_id = $1
' LANGUAGE SQL STABLE;
And then tried:
SELECT v.div_name, d.dept_id, e.emp_id, e.emp_name
FROM divisions v
INNER JOIN departments d
ON d.div_id = v.div_id
INNER JOIN employees e
ON get_empdept(e.emp_id) = d.dept_id
WHERE v.div_id = 123;
However since the function is not immutable (since it does a query), I can't create an index, and the join always does a
seq scan.
I also thought to create a view, but I don't believe Postgres supports indexed views. It was always using a seq scan too.
The above examples are actually quite simplified, as several other tables get joined along the way, I'm not sure a UNION
would work or not, how would it exclude the ones that match the dept_id in the emp table for those emps that match on
the over-ride table?
Any suggestions?
Thanks
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: I'm stumped
2004-12-20 16:30 I'm stumped Doug Y <dylists@ptd.net>
@ 2004-12-21 22:39 ` thomas.silvi <thomas.silvi@laposte.net>
2004-12-22 01:42 ` Re: I'm stumped Iain <iain@mst.co.jp>
0 siblings, 1 reply; 3+ messages in thread
From: thomas.silvi @ 2004-12-21 22:39 UTC (permalink / raw)
To: Doug Y <dylists@ptd.net>; +Cc: pgsql-sql
Doug Y a écrit :
> I can't figure out an efficient way to do this. Basically I had a
> typical 3-tier relationship:
> (Employee -> Department -> Division)
> However, at some point the need to move employees arose, but instead
> of changing the key in the emp table, we now have an over-ride table,
> so a history can be tracked.
>
> If I want to get the info for a particular employee, its a pretty
> simple deal, however, getting all the employees for a dept or division
> has become troublesome.
>
> A very simplified schema:
> divisions ( div_id, div_name );
> departments ( dept_id, dept_name, div_id );
> employees ( emp_id, emp_name, dept_id );
> emp_dept ( emp_id, dept_id, active, changed_by, changed_when );
>
> The original way that worked well:
> SELECT v.div_name, d.dept_id, e.emp_id, e.emp_name
> FROM divisions v
> INNER JOIN departments d
> ON d.div_id = v.div_id
> INNER JOIN employees e
> ON e.dept_id = d.dept_id
> WHERE v.div_id = 123;
>
> What was initially tried:
> SELECT v.div_name, COALESCE(ed.dept_id, d.dept_id), e.emp_id, e.emp_name
> FROM divisions v
> INNER JOIN departments d
> ON d.div_id = v.div_id
> INNER JOIN employees e
> ON e.dept_id = d.dept_id
> LEFT JOIN emp_dept ed
> ON ed.emp_id = e.emp_id AND ed.active = true
> WHERE v.div_id = 123;
> This query is flawed, as it still always puts the employees in their
> original div, but reports the new dept. Which we didn't catch as a
> problem until emps were moved to depts in a different division.
>
> I tried creating a function:
> CREATE OR REPLACE FUNCTION get_empdept(int4) RETURNS int4 AS '
> SELECT CASE WHEN ed.dept_id IS NOT NULL
> THEN ed.dept_id
> ELSE e.dept_id END
> FROM employees AS e
> LEFT JOIN emp_dept AS ed
> ON ed.emp_id = e.emp_id AND ed.active = true
> WHERE e.emp_id = $1
> ' LANGUAGE SQL STABLE;
>
> And then tried:
> SELECT v.div_name, d.dept_id, e.emp_id, e.emp_name
> FROM divisions v
> INNER JOIN departments d
> ON d.div_id = v.div_id
> INNER JOIN employees e
> ON get_empdept(e.emp_id) = d.dept_id
> WHERE v.div_id = 123;
>
> However since the function is not immutable (since it does a query), I
> can't create an index, and the join always does a seq scan.
>
> I also thought to create a view, but I don't believe Postgres supports
> indexed views. It was always using a seq scan too.
>
> The above examples are actually quite simplified, as several other
> tables get joined along the way, I'm not sure a UNION would work or
> not, how would it exclude the ones that match the dept_id in the emp
> table for those emps that match on the over-ride table?
>
> Any suggestions?
>
Hello,
have you an index on emp_dept on emp_id, dept_id ?
what about this ?
SELECT v.div_name, d.dept_id, e.emp_id, e.emp_name
FROM divisions v
INNER JOIN departments d ON d.div_id = v.div_id
INNER JOIN employees e ON e.dept_id = d.dept_id
WHERE NOT EXISTS (SELECT 1 FROM emp_dept ed WHERE ed.emp_id = e.emp_id)
AND v.div_id = 2
UNION ALL
SELECT v.div_name, d.dept_id, e.emp_id, e.emp_name
FROM divisions v
INNER JOIN departments d ON d.div_id = v.div_id
INNER JOIN emp_dept ed ON d.dept_id = ed.dept_id
INNER JOIN employees e ON e.emp_id = ed.emp_id
WHERE ed.active=true
AND v.div_id = 2
Regards,
Thomas
> Thanks
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: I'm stumped
2004-12-20 16:30 I'm stumped Doug Y <dylists@ptd.net>
2004-12-21 22:39 ` Re: I'm stumped thomas.silvi <thomas.silvi@laposte.net>
@ 2004-12-22 01:42 ` Iain <iain@mst.co.jp>
0 siblings, 0 replies; 3+ messages in thread
From: Iain @ 2004-12-22 01:42 UTC (permalink / raw)
To: thomas.silvi@laposte.net; Doug Y <dylists@ptd.net>; +Cc: pgsql-sql
Hi,
I didn't bother analysing this too deeply, so keep that in mind when you
read my reply ;-)
However the point that set off alarm bells for me was this statement "we now
have an over-ride table".
It seems to me that you shouldn't be over-riding anything, and the emp_dept
table should just be history - ie "emp_dept_his", and it wouldn't need the
"active" column.
If you need history then you could use a trigger or rule to insert the
history record whenever the emloyee changes department.
The key question here is "Why wouldn't you change the key in the emp table
when that key represents the employee's department?". It seems like a
handbook case to me but maybe I missed something.
regards
Iain
>> I can't figure out an efficient way to do this. Basically I had a typical
>> 3-tier relationship:
>> (Employee -> Department -> Division)
>> However, at some point the need to move employees arose, but instead of
>> changing the key in the emp table, we now have an over-ride table, so a
>> history can be tracked.
>>
>> If I want to get the info for a particular employee, its a pretty simple
>> deal, however, getting all the employees for a dept or division has
>> become troublesome.
>>
>> A very simplified schema:
>> divisions ( div_id, div_name );
>> departments ( dept_id, dept_name, div_id );
>> employees ( emp_id, emp_name, dept_id );
>> emp_dept ( emp_id, dept_id, active, changed_by, changed_when );
>>
>> The original way that worked well:
>> SELECT v.div_name, d.dept_id, e.emp_id, e.emp_name
>> FROM divisions v
>> INNER JOIN departments d
>> ON d.div_id = v.div_id
>> INNER JOIN employees e
>> ON e.dept_id = d.dept_id
>> WHERE v.div_id = 123;
>>
>> What was initially tried:
>> SELECT v.div_name, COALESCE(ed.dept_id, d.dept_id), e.emp_id, e.emp_name
>> FROM divisions v
>> INNER JOIN departments d
>> ON d.div_id = v.div_id
>> INNER JOIN employees e
>> ON e.dept_id = d.dept_id
>> LEFT JOIN emp_dept ed
>> ON ed.emp_id = e.emp_id AND ed.active = true
>> WHERE v.div_id = 123;
>> This query is flawed, as it still always puts the employees in their
>> original div, but reports the new dept. Which we didn't catch as a
>> problem until emps were moved to depts in a different division.
>>
>> I tried creating a function:
>> CREATE OR REPLACE FUNCTION get_empdept(int4) RETURNS int4 AS '
>> SELECT CASE WHEN ed.dept_id IS NOT NULL
>> THEN ed.dept_id
>> ELSE e.dept_id END
>> FROM employees AS e
>> LEFT JOIN emp_dept AS ed
>> ON ed.emp_id = e.emp_id AND ed.active = true
>> WHERE e.emp_id = $1
>> ' LANGUAGE SQL STABLE;
>>
>> And then tried:
>> SELECT v.div_name, d.dept_id, e.emp_id, e.emp_name
>> FROM divisions v
>> INNER JOIN departments d
>> ON d.div_id = v.div_id
>> INNER JOIN employees e
>> ON get_empdept(e.emp_id) = d.dept_id
>> WHERE v.div_id = 123;
>>
>> However since the function is not immutable (since it does a query), I
>> can't create an index, and the join always does a seq scan.
>>
>> I also thought to create a view, but I don't believe Postgres supports
>> indexed views. It was always using a seq scan too.
>>
>> The above examples are actually quite simplified, as several other tables
>> get joined along the way, I'm not sure a UNION would work or not, how
>> would it exclude the ones that match the dept_id in the emp table for
>> those emps that match on the over-ride table?
>>
>> Any suggestions?
>>
> Hello,
> have you an index on emp_dept on emp_id, dept_id ?
> what about this ?
>
> SELECT v.div_name, d.dept_id, e.emp_id, e.emp_name
> FROM divisions v
> INNER JOIN departments d ON d.div_id = v.div_id
> INNER JOIN employees e ON e.dept_id = d.dept_id
> WHERE NOT EXISTS (SELECT 1 FROM emp_dept ed WHERE ed.emp_id = e.emp_id)
> AND v.div_id = 2
> UNION ALL
> SELECT v.div_name, d.dept_id, e.emp_id, e.emp_name
> FROM divisions v
> INNER JOIN departments d ON d.div_id = v.div_id
> INNER JOIN emp_dept ed ON d.dept_id = ed.dept_id
> INNER JOIN employees e ON e.emp_id = ed.emp_id
> WHERE ed.active=true
> AND v.div_id = 2
>
> Regards,
> Thomas
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2004-12-22 01:42 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-12-20 16:30 I'm stumped Doug Y <dylists@ptd.net>
2004-12-21 22:39 ` thomas.silvi <thomas.silvi@laposte.net>
2004-12-22 01:42 ` Iain <iain@mst.co.jp>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox