Re: Heirarchical Data Structures. MS Access. Together?

From: --CELKO-- <joe.celko_at_northface.edu>
Date: 30 Jul 2003 15:46:18 -0700
Message-ID: <a264e7ea.0307301446.26f47f9c_at_posting.google.com>

>> Using nested set I assume one would have to use triggers or some
similar construct to prevent the same thing. Perhaps one could express it using a check constraint? <<

No, pure DRI will work. In a nested sets model, the organizational chart is in one table with the job titles and the personnel that hold those jobs are in another table. George Bush holds the position of President of the United States, etc.

When I fire --oh, I mean "downsize" -- an employee, I set the value of the employee in that position to '{{vacant}}', a mythical employee value.

CREATE TABLE OrgChart
(emp_id CHAR(10) DEFAULT '{{vacant}}' NOT NULL

         REFERENCES Personnel(emp_nbr)
         ON UPDATE CASCADE
         ON DELETE SET DEFAULT,

 position CHAR(10) NOT NULL,
 lft INTEGER NOT NULL,
 rgt INTEGER NOT NULL,
 ...);

If I drop a position from the organization, then I have to make a lot of decisions about what I want the tree to look like. Ask anyone who has been thru a re-oprganization at a Dot-Com ...

If I simply delete a row in the Organzational Chart, then the subordinates report directly to the boss of their former boss (and the (ldft, rgt) pairs need to be reneumbered). Received on Thu Jul 31 2003 - 00:46:18 CEST