Re: Heirarchical Data Structures. MS Access. Together?

"--CELKO--" <71062.1056_at_compuserve.com> wrote in message news:c0d87ec0.0307220645.2ccab82f_at_posting.google.com...
> Another way of representing trees is to show them as nested sets.
> Since SQL is a set oriented language, this is a better model than the
> usual adjacency list approach you see in most text books. Let us
> define a simple OrgChart table like this, ignoring the left (lft) and
> right (rgt) columns for now. This problem is always given with a
> column for the employee and one for his boss in the textbooks. This
> table without the lft and rgt columns is called the adjacency list
> model, after the graph theory technique of the same name; the pairs of
> emps are adjacent to each other.
>
> CREATE TABLE OrgChart
> (emp CHAR(10) NOT NULL PRIMARY KEY,
> lft INTEGER NOT NULL UNIQUE CHECK (lft > 0),
> rgt INTEGER NOT NULL UNIQUE CHECK (rgt > 1),
> CONSTRAINT order_okay CHECK (lft < rgt) );
>
> OrgChart
> emp lft rgt
> ======================
> 'Albert' 1 12
> 'Bert' 2 3
> 'Chuck' 4 11
> 'Donna' 5 6
> 'Eddie' 7 8
> 'Fred' 9 10
>
> The organizational chart would look like this as a directed graph:
>
> Albert (1,12)
> / \
> / \
> Bert (2,3) Chuck (4,11)
> / | \
> / | \
> / | \
> / | \
> Donna (5,6) Eddie (7,8) Fred (9,10)

Very nice! Once I saw what you were getting at, the strategy made perfect sense to me. It certainly adds efficiency in several nifty ways, especially in finding removed parents of children.

Incidentally, thanks to all who responded to this little query of mine. All of your suggestions have been most helpful and interesting, and I've gotten several ideas I haven't thought of before. When I get this database working, I may post it to a.d.ms-access as the technique may be of interest. Thanks again to all!

Jace Received on Thu Jul 24 2003 - 04:43:50 CEST