Re: Heirarchical Data Structures. MS Access. Together?

"Jace Harker" <jharker_at_marlboro.edu> wrote:

>"Mark Johnson" <102334.12_at_compuserve.com> wrote in message
>news:en1ahv0thvpvk6uv92mb1du85c546q44md_at_4ax.com...
>> I think what the relational people would suggest is 'unrolling' the
>> hierarchy in an index field, listing in directory fashion the unique
>> superior keys, in order (e.g. key1/key2/key5/ for a particular item,
>> key10). Then the relational model fully applies, because you can use
>> that field.

>First and foremost, thanks very much for the input!

>Now, I'm not completely sure I understand the above- do you mean several
>tables (one for each level of the heirarchy), with the later ones having
>multi-field primary keys?

Again, it might not be applicable to your problem. If you only have the one level of sub-categories, you probably aren't looking at any more of a hierarchy than you normally get in creating dependent tables in a relational db. But if the categories start to multiply, and the levels go three, four or more, and they can be changed at whim, then you could think of that bit, at least, as nothing more than a label for each person, not a set of nested tables. So, in one table, you'd have this list of nested, 'outline' labels, and a unique record id - like an autonumber - that you'd store in the record for each person. If you stored that 'link list' as the key, or as a second key, then you could also retrieve by any level, with a function to find a text match in any field (e.g. fRf(key,"key3), when the key might be "key1/key3/key4" ).

>Hospital (O)
>Community Group (O)
> [Town] (O)
> Town 1 (O)
> Town 2 (O)
> Town 3 (O)
> [Position] (P)
> Local chairperson (P)
> Local volunteer (P)
> State Liason (P)
>Private Practice (O)

>I have expanded the "Community Group" option as an example. If this were
>the node tree, then initially a Person would not have any options, because
>the top-level nodes apply only to Organizations. But if the Person is
>affiliated with an Organization, and that Organization has the "Community
>Group" option selected, then the Person now has [Position] and its
>subsidiaries as data options.

You've got a city table and employee table. And you've got a group table. And each record has the relevant info, or has dependent tables for certain fields, etc. So you have the info in three tables, but now you want to establish the links/hierarchy above. You'd just use a table id or type and the unique key for each table. And those two fields would go in your 'outline' table. Then you'd create that self-referential, table of contents type table along any scheme you prefer. Again, I would suggest adjacency lists. But there seem to be a lot of schemes you could use. The adjacency model would have the unique key, and a superior id link using the values from the unique key, maybe a separate sort field (which isn't needed with other schemes).

>Actually, this is very close to what I've done. I have one table called
>tblNodes, which is essentially a self-referential Treeview nodelist with
>fields NodeID (autonumbered), ParentID, NodeName, and NodeDataType. In fact
>I am using a treeview to display the nodes. I then have another table
>tblNodeAppliesTo with two fields, NodeID, and KindID, where KindID is
>specified in another table and denotes different kinds of Entity (Person,
>Organization, Coalition).

Ouch.

>> hierarchy. There is the 'flat index'/'materialized path' method,
>> mentioned in the first paragraph, as an alternative. And I'm sure
>> there are various methods for storing a self-referential, hierarchical
>> tables, such as Celko's 'nested sets', which articles he has
>> frequently posted to Usenet and assorted web boards.

>I'll look for that, thanks!

The Celko thing is one of those other schemes. And just 'unrolling' the nesting with a key such as mentioned at the top, here, is another straightforward approach. A man named Vadim Tropashko suggested a 'middle ground' between the two. And I'm sure there are others. I just prefer, at this point, the adjacency model, as difficult as it may be if not to maintain, then to query. Received on Thu Jul 17 2003 - 00:17:49 CEST