Designing a changing Hierarchy

Designing a changing Hierarchy

Post by Michael G. Schneide » Fri, 21 Feb 2003 20:33:26



Suppose you have an typical organisation, consisting of departments. Each
department is either a root department, or it has one superior department.
Usually I would implement this by having a "Daddy" column, pointing to the
superior department or being NULL.

However, in this special case, the hierarchy changes and these changes have
to be saved inside the database. A department may be deleted, a new
department may be inserted, or a department may be moved under a different
department.

Typical questions might be...

[1] Which was the daddy of DEP1 on Jan 3rd, 2000?

[2] Which were the children of DEP2 on Aug 20th, 2002?

[3] When did DEP3 come into existance, when did it disappear?

Is this a standard scenario in database design? Is there a prefered way to
solve it? Can it be done with well performing SELECTs?

Michael G. Schneider

 
 
 

Designing a changing Hierarchy

Post by Luke Mile » Sat, 22 Feb 2003 01:13:14


I have found it to be a standard scenario at most
companies that I have worked for. Personnaly I would make
a tabel that has the "Daddy/Parent" column that has a
foriegn key relationship back to the parent department
(in the same table) and add the columns of effective_date
and end_date. Queryies on this are not the simplest, but
if you do it this way you could answer the questions
below by doing queries similar to the following.

Note this assumes that end_date is null for active
assignments, and is filled in for inactive assignments.
It also assumes the DEP_ID is a primary key (new primary
keys are assigned if a department is reassigned). The
primary key could also be a compound key made up of
ID,Effective_date,End_date but I think that doing it that
way complicates things. You can have a seperate column
that has a key to tie all instances of a deparment
together. In this case I will use DEP_Name for this "key"

[1]Select parent.dep_name,child.dep_name from
dep_table child, dep_table parent
where child.dep_parent_id=parent.dep_id
and child.dep_name='DEP1'
and child.effective_date<='1/3/2002' and
(child.end_date>='1/3/2002' or child.end_date is null)

[2]select child.dep_name from dep_table child where
child.dep_parent_id=(select dep_id from dep_table where
dep_name='DEP2' and effective_date<='8/20/2002' and
(end_date>='8/20/2002' or end_date is null)

[3]select min(effective_date),max(end_date) from
dep_table where dep_name='DEP3'

[4] In this case we want to see all the parents of DEP4
during the date range of Jan 1 2002 and Dec 31 2002.

select parent.dep_name from
dep_table child, dep_table parent
where
child.dep_parent_id=parent.dep_id
and
effective_date<='12/31/2002' and (end_date>='01/01/2002'
or end_date is null)

Note that the start date of our range is matched to the
end_date in the table, and the stop date of our range is
matched to the effective_date in the table. This allows
for situations where overlapping occurs, e.g. the
department started its assignment before our start date
and ended after our stop date.

Quote:>-----Original Message-----
>Suppose you have an typical organisation, consisting of
departments. Each
>department is either a root department, or it has one

superior department.
Quote:>Usually I would implement this by having a "Daddy"

column, pointing to the
Quote:>superior department or being NULL.

>However, in this special case, the hierarchy changes and
these changes have
>to be saved inside the database. A department may be
deleted, a new
>department may be inserted, or a department may be moved
under a different
>department.

>Typical questions might be...

>[1] Which was the daddy of DEP1 on Jan 3rd, 2000?

>[2] Which were the children of DEP2 on Aug 20th, 2002?

>[3] When did DEP3 come into existance, when did it
disappear?

>Is this a standard scenario in database design? Is there
a prefered way to
>solve it? Can it be done with well performing SELECTs?

>Michael G. Schneider

>.


 
 
 

Designing a changing Hierarchy

Post by Joe Celk » Sat, 22 Feb 2003 02:57:06


I have a book on Trees & Hierarchies in SQL coming out this year, but I
did not deal with temp*changes in an organization.  What about this
approach?

CREATE TABLE OrgChartHistory
(parent CHAR(8), -- null means root
 child CHAR(8) NOT NULL,
 start_date DATETIME NOT NULL,
 end_date DATETIME, -- null means on-going
 << constraints for adjacency list model>>);

[1] Which was the daddy of DEP1 on Jan 3rd, 2000?

SELECT '2000-01-03', parent, child
  FROM OrgChartHistory AS H1
 WHERE '2000-01-03' BETWEEN H1.start_date
           AND COALESCE(H1.end_date, CURRENT_TIMESTAMP)
   AND child = 'DEP1';

[2] Which were the children of DEP2 on Aug 20th, 2002?

SELECT '2002-08-20', parent, child
  FROM OrgChartHistory AS H1
 WHERE '2002-08-20' BETWEEN H1.start_date
           AND COALESCE(H1.end_date, CURRENT_TIMESTAMP)
   AND parent = 'DEP2';

[3] When did DEP3 come into existance, when did it disappear?

SELECT child, start_date,
       COALESCE (end_date, 'Still active') AS end_date
  FROM OrgChartHistory AS H1
 WHERE child = 'DEP3';

--CELKO--
 ===========================
 Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.veryComputer.com/ ***
Don't just participate in USENET...get rewarded for it!

 
 
 

Designing a changing Hierarchy

Post by Michael G. Schneide » Sat, 22 Feb 2003 18:39:28




Quote:> Suppose you have ...

Thank's a lot to Luke and Joe. Your comments have been very helpfull.

Michael G. Schneider

 
 
 

Designing a changing Hierarchy

Post by Mike Sherril » Fri, 28 Feb 2003 11:49:22


On Thu, 20 Feb 2003 12:33:26 +0100, "Michael G. Schneider"

[snip]

Quote:>Typical questions might be...

>[1] Which was the daddy of DEP1 on Jan 3rd, 2000?

I've worked for a lot of places that needed "org chartly" kinds of
information.  None of them cared what their org chart looked like
three years ago.

My point is not that what you want to do can't be done, shouldn't be
done, or might be hard.  My point is that what you're doing isn't what
I would call typical.  (In the case of org charts.  It is somewhat
more typical in other contexts.)

[snip]

Quote:>Is this a standard scenario in database design?

Run, don't walk, to Snodgrass's book _Developing Time-Oriented
Database Applications in SQL_.

--
Mike Sherrill
Information Management Systems

 
 
 

1. Problem with Application Design Transformer in Function Hierarchy Diagrammer in Oracle Designer 6

I have a problem with Application Design Transformer in
Function Hierarchy Diagrammer in Oracle Designer 6
my start function contains 43 sub functions

the tranformer works like this :

Initializing...
Creating temporary tables...
Querying Function details...
Identifying Modules...
Creating candidate Modules...
Creating General Module :  (name of sub functions)
.
Creating Module text...
Creating Module Function associations...
Creating Module Business Unit associations...
Creating Module Arguments...
Creating Module Components and Table usages...
Module : (name of sub functions)
- creating Window : (name of sub functions)
- creating Module Component Table Usage for Table : (name of
entity)
..
Creating Data Bound Items...
Creating Argument Item usages...
Creating Module Table usages...
Creating Module Column usages...
Generate data usages has completed successfully.

the percentage completion figure is 95%

then comes the following error message
RME-00224: Failed to close activity
with details
RME-00222: Failed to dispatch operation to Repository
RME-00011: Operation 'close' on ACTIVITY has failed
RME-00020: Internal API error - ORA-01562: failed to extend
rollback segment number 13 | ORA-01628: max # extents (121)
reached for rollback segment RB12

when I click OK (no other possibility) I get this message

The utility has stopped due to failure.

I  checked the tablespace ? it is not to small

Name Status Size (M) Used (M)
ROLLBACK DATA ONLINE 65.000 33.750
SYSTEM ONLINE 270.000 260.137
TEMPORARY_DATA ONLINE 7.000 2.637
USER_DATA ONLINE 8.000 4.543
WIC_DATA ONLINE 135.000 93.195
WIC_RBS ONLINE 60.000 3.020

when I check in Design Editor, the module list is empty

I dont know what happens
Please help

--
----------------------------------------------------------------------------

Cornelia
Wetzel

Fachhochschule Stralsund http://www.fh-stralsund.de

2. Access SQL Server 6.5 data from 7.0

3. query design for XML from hierarchy table

4. pgsql/src/backend/tcop postgres.c

5. Table Design: Hierarchy

6. Oracle & Optical Storage

7. design for an Organization's Hierarchy

8. How to Compress the "SYSTEM" tablespace

9. Design issue: nested menu options / hierarchy

10. hierarchy table design question

11. Advice needed on Hierarchy Design

12. replication - design change - can you remove it from articlkes - change and readd

13. Setting parameters at Hierarchy within a hierarchy