Re: Using Materialized Path to create a paths table

From: Neo <neo55592_at_hotmail.com>
Date: 25 May 2006 10:26:10 -0700
Message-ID: <1148577970.523387.113050_at_38g2000cwa.googlegroups.com>

EmployeeId ParentEmployeeId MaterializedPath

1                   <null>                    1.
2                   1                           1.1.
3                   1                           1.2.
4                   2                           1.1.1

To find the descendants of a node: select Descendant.EmployeeId from Employee as Ancestor inner join Employee as Descendant on Descendant.MaterializedPath like Ancestor.MaterializedPath + '%' where Ancestor.EmployeeId = 1

To find the ancestors: select Ancestor.EmployeeId from Employee as Descendant inner join Employee as Ancestor on Descendant.MaterializedPath likeAncestor.MaterializedPath + '%' where Descendant.EmployeeId = 4

This is very expensive for any non-trivial number of nodes ...build a denormalized EmployeeAncestor table... becomes very much cheaper to calculate the ancestors of a node: select Ancestor.EmployeeId from EmployeeAncestor Descendant inner join employee Ancestor on Ancestor.EmployeeId = Descendant.AncestorEmployeeId where Descendant.EmployeeId = 4

To find descendant: select Descendant.EmployeeId from Employee Ancestor inner join EmployeeAncestor Descendant on Descendant.AncestorEmployeeId = Ancestor.EmployeeId where Ancestor.EmployeeId = 1

> Any comments?

Below is a similar example which doesn't require user to deal with IDs, NULLs, complex joins or materialized paths?

(; Create persons)
(new 'dawn 'person)
(new 'marshall 'person)
(new 'bob 'person)
(new 'john 'person)

(; Create verbs employee and boss)
(new 'employee 'verb)
(new 'boss 'verb)
(create employee reciprocal boss)
(create boss reciprocal employee)

(; Create boss/employee hierarchy)
(createWRR dawn employee marshall)
(createWRR dawn employee bob)
(createWRR marshall employee john)

(; Find dawn's direct employees)
(; Finds marshall and bob)
(select dawn employee *)

(; Find bob's direct boss)
(; Finds dawn)
(select bob boss *)

(; Is john's boss dawn)
(; True)
(verifyRel john boss dawn)

(; Is dawn's employee john)
(; True)
(verifyRel dawn employee john)

For another similar examples, see dbfordummies.com/example/ex007.asp Received on Thu May 25 2006 - 19:26:10 CEST