> How does one go about creatig the correct inheritence of a tree structure
> If I have a table containing a tree structure. The parent table could be
> related to a cild table with some aditional related properties. Under normal
> circumstance this is easy.
> But take a tree structure where it stands to reason tht the child nodes of
> the tree would inherit from the parent node
> So in an employee table, where a Boss has people underhim, and that boss is
> related to a department, how does one then inherit that department for all
> child nodes(employees)
> My first thought is a bridging table to.  But then this could be a
> management nightmare, because if the boss changes their department, we have
> to itterate throu all employee nodes.
>
> Am I makeing sense here
> Your help is apreciated

"Tav" <tavis.p***@staticsystems.co.uk> wrote in message
news:1154609024.538633.316310@i3g2000cwc.googlegroups.com...
> Robert Bravery wrote:
> > How does one go about creatig the correct inheritence of a tree
structure
> > If I have a table containing a tree structure. The parent table could be
> > related to a cild table with some aditional related properties. Under
normal
> > circumstance this is easy.
> > But take a tree structure where it stands to reason tht the child nodes
of
> > the tree would inherit from the parent node
> > So in an employee table, where a Boss has people underhim, and that boss
is
> > related to a department, how does one then inherit that department for
all
> > child nodes(employees)
> > My first thought is a bridging table to.  But then this could be a
> > management nightmare, because if the boss changes their department, we
have
> > to itterate throu all employee nodes.
> >
> > Am I makeing sense here
> > Your help is apreciated
>
> Robert,
> With the knowledge that I may put-my-foot-in-it and
> bark-up-the-wrong-tree I have tried to answer this issue as the best I
> understand it.  If I'm complete off the mark, you get some same SQL to
> play around with anyway.
> With help of Scott Adam's Dilbert I will explain the following SQL:
> (run this SQL on a test or development database)
>
> CREATE TABLE dbo.Department(
> DepartmentID int IDENTITY(1,1) NOT NULL
> CONSTRAINT pk_DepartmentID PRIMARY KEY NONCLUSTERED,
> FullName varchar(90) NULL,
> MeetingLocation varchar(90) NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE dbo.Employee(
> EmployeeID int IDENTITY(1,1) NOT NULL
> CONSTRAINT pk_EmployeeID PRIMARY KEY NONCLUSTERED,
> BossID int NULL,
> DepartmentID int NULL,
> FullName varchar(60) NULL
> ) ON [PRIMARY]
> GO
>
> INSERT INTO dbo.Department(FullName, MeetingLocation) VALUES('Board of
> Directors', 'Plush Boardroom')
> INSERT INTO dbo.Department(FullName, MeetingLocation) VALUES('IT',
> 'down the Nag''s Head')
>
> INSERT INTO dbo.Employee(BossID, DepartmentID, FullName) VALUES(NULL,
> 1, 'Pointed-haired Boss')
> INSERT INTO dbo.Employee(BossID, DepartmentID, FullName) VALUES(1, 2,
> 'Dilbert')
> INSERT INTO dbo.Employee(BossID, DepartmentID, FullName) VALUES(1, 2,
> 'Wally')
>
> PRINT 'Simple parent-child (Employee-Boss) relationship where employees
> have not inherited their boss''s department:'
> SELECT E.FullName AS EmployeeName,
> D.FullName AS Department,
> COALESCE(Boss.FullName, 'the top man!') AS Boss,
> D.MeetingLocation
> FROM Employee E
> INNER JOIN Department D ON E.DepartmentID = D.DepartmentID
> LEFT JOIN Employee Boss ON E.BossID = Boss.EmployeeID
>
>
> PRINT 'Simple parent-child (Employee-Boss) relationship where employees
> have inherited their boss''s department:'
> SELECT E.FullName AS EmployeeName,
> COALESCE(D.FullName, BossDept.FullName) AS Department,
> COALESCE(Boss.FullName, 'the top man!') AS Boss,
> COALESCE(D.MeetingLocation, BossDept.MeetingLocation) AS
> MeetingLocation
> FROM Employee E
> LEFT JOIN Employee Boss ON E.BossID = Boss.EmployeeID
> LEFT JOIN Department D ON Boss.DepartmentID = D.DepartmentID
> LEFT JOIN Department BossDept ON E.DepartmentID = BossDept.DepartmentID
>
> Firstly it's always better to keep the atomicity of the data, and avoid
> switching (changing) data values upon events.  It is far better to keep
> the data values in tact and switch (change) the values through stored
> procedures (ie. SQL script).
> The first SELECT statement shows the simple inheritance of
> Employee-Boss as a 'self-join' of the dbo.Employee table.  This does
> not show inheritance of the Boss's department.
> The second SELECT statement shows the inheritance of the employees'
> boss' department.  Wally and Dilbert can now hold meetings in the plush
> boardroom rather than the smoky and dingy Nag's Head.  If the boss
> changes his department, his employee will automatically change with
> them so 'no iterate  through all employee nodes'.  See the following
> SQL:
>
> INSERT INTO dbo.Department(FullName, MeetingLocation) VALUES('Executive
> Officers', 'CEO''s Private Jet')
>
> UPDATE Employee SET DepartmentID = 3 WHERE EmployeeID = 1
>
> PRINT 'Simple parent-child (Employee-Boss) relationship where employees
> have inherited their boss''s department:'
> SELECT E.FullName AS EmployeeName,
> COALESCE(D.FullName, BossDept.FullName) AS Department,
> COALESCE(Boss.FullName, 'the top man!') AS Boss,
> COALESCE(D.MeetingLocation, BossDept.MeetingLocation) AS
> MeetingLocation
> FROM Employee E
> LEFT JOIN Employee Boss ON E.BossID = Boss.EmployeeID
> LEFT JOIN Department D ON Boss.DepartmentID = D.DepartmentID
> LEFT JOIN Department BossDept ON E.DepartmentID = BossDept.DepartmentID
>
> Dilbert and Wally are now packing their suitcases with sunglasses, sun
> cream and Bermuda shorts for their next meeting _ flying towards Aruba!
>
> Regards,
>
>     -Tav.-
> Tavis Pitt
>