multi-level hierarchical structure

[mscertified]

A system I am developing will have as a key component a 3-level hierarchical
structure. I know this can be represented by 3 separate tables A, B, C where
rows in B are linked to A and rows in C are linked to B. However, I can also
represent this structure by a single table by having rows refer back to the
primary key of other rows, thus:
Key
Name
Parent Key (points to Key)
In this design the top level rows have no parent and the lowest level rows
have no other row pointing to them. This seems the most elegant solution and
has the advantage of supporting any number of levels in a single table.
However, I can forsee the queries to extract data being rather tricky.

Does anyone have any input on which representation would be best?

 

[Pat Hartman\(MVP\)]

It is easy enough to create queries for this type of structure as long as
you know what the maximum depth is. I've done 6 levels without problem.
This is actually the ideal structure for a TreeView control if that suits
your design plans. If you write VBA to "walk the tree", you can make it
recursive and so process a tree with an infinite depth.

Three separate tables will be slightly easier to work with and easier for
most people to understand. However, that is not technically the correct
design and would certainly cause a problem if there is any possibility at
all that the depth could increase.

 

[Amy Blankenship]

Also, if you're accessing the database from outside Access, (for example
ASP) you can use Shape Queries. AFAIK, the type of connection access has
with itself does not support data shaping.

HTH;

Amy

 

[Tom Ellison]

Dear Rupert:

I would recommend the self referencing one-table approach.

Be sure to have a column in the table that represents the levels in the
hierarchy. For this, I commonly use an alpha-numeric description, placing
thise in a foreign table which then numbers the levels. The numbers do not
have to be consecutive (nice if you need to insert more levels between
existing ones later) but they do need to represent the hierarchy. It might
be like:

A 10
B 20
C 30

You will probably want a query that gives these and Ranks them. The Rank
column is added by this query so it looks like:

A 10 1
B 20 2
C 30 3

Do not store this in the table. If you insert levels later, it must
automatically re-rank them.

In the form for maintaining this table, a Parent row must be selected for
each element entered. Note that, by your convention, the A level would have
no parent.

Now what you are creating is a tree structure. It needs a root. I would
add one:

Root 0
A 10
B 20
C 30

This can be the parent of all A level elements. The value 0 should be used
to make this a "special" level. No element can be added to the table at
this level. The one and only Root entry must be made manually before user
can begin adding their own elements.

An important factor in determining how this will work is whether the tree
"skips levels" or not. Is the parent of every level B a level A, or can
they exist under the Root? Can the parent of a level C be a Level A?

There will be a UNION query that generates all the possibilities. The
outcome of this query can be made to be an "indented outline" form. This
form is probably the easiest way to explain what is being produced, so I'll
give a sample.

Say all the elements at level A are labelled A1, A2, A3, A4, . . .

All the elements at Level B are labelled B1A1, B2A1, B3A1, B4A3, . . .

Here, B1 is under A1. The actual names do not have to look like this. I
have chosen this only to illustrate.

All the elements at Level C are labelled C1B1A1, C2B3A1, C3B3A1, C4B4A3

The outline form for all this looks like this:

A1
B1A1
C1B1A1
B2A1
B3A1
C2B3A1
C3B3A1
A2
A3
B4A3
C4B4A3
A4

Again, the actual names attached would not look like these. They might be
John, Mary, Pete, and Carol. But the outline form above is the key to both
understanding what is built and how it functions. You can not only get a
report to sort like this, but also to indent the rows as shown for ease of
interpretation.

This is a fair sized study, and I won't go into any more for now. Let me
know if you want more. Please present some more details of what you want,
and answer the question about skipping levels. We'll go into the table
structure next, then the UNION query. What happens will depend on your
responses.

Tom Ellison

 

[Krizhek]

Hey Tom,

Do you happen to have an example database for the example. Or maybe some
more info?