Re: Heirarchical Data Structures. MS Access. Together?

"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?

> But what you suggest, at the end, isn't really a hierarchy as just a
> relation. So whether Access/Jet (which is what I assume you mean), or
> Access/whatever-proprietary-SQL-server, you'd just follow the rules
> for creating a normalized relational db.
>
> Dr. Smith's record, btw, doesn't "allow" anything. Since the staff is
> unique to the hospital, not incl. doctors of course, whether temp
> agency or no, you'd just assign them to a staff table for each doctor;
> likely filtering by hospital ID in the subform before beginning the
> selection.

I see what you mean. This wasn't quite how I was looking at it, though. I may not have been entirely clear about the nature of my data. Here's a less simplified example of the node structure. For the moment, we'll assume that all nodes denote boolean data except the [placeholders], which are in brackets. The letter in parentheses following the node name denotes whether the node applies to a Person or Organization:

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)

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.

This is what I meant by relationships "unlocking" data options. Is there a way to put the above into a more normal database structure? I kind of infer from what you said that there may be?

> However, that list at top, which could have more nested levels - that
> is a tree, a nodeset, a hierarchy; except for the scalar values (time
> spent, etc) which could go with separate tables whose rows are laid
> out, essentially, as type structures, in some field in a row for that
> individual. It's a pick'em label list, basically. The bottom 'leaf'
> would have a unique id. And that would be stored in some field of the
> row for the individual. I think that list, above, would be best
> presented in a treeview. I would use adjacency lists to store the

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).

Then I store the information in a third table, tblData, where the key is a composite of EntityID and NodeID, and the third field is simply a string to store the data (actually, I have several fields for different kinds of data, but we won't go there. :-)

> 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! Received on Wed Jul 16 2003 - 18:38:16 CEST