Re: Heirarchical Data Structures. MS Access. Together?

"Jace Harker" <jharker_at_marlboro.edu> wrote:

>1. Health Care Worker
> A. # of Patients per week
> B. Doctor?
> C. Nurse?
> D. Assistant?
>2. NASA Employee
> A. Astronaut?
> B. Engineer?
> C. PR Expert?
> D. Amount of time spent in space
>3. Boy Scouts
> A. Cub Scout?
> B. Boy Scout?
> C. Eagle?
> D. Den Mother?
> E. Age you started

>These might apply to a person. So the person could be either #1 or #2, but
>not both at once, but the person could be #3 or not, regardless. If the
>person is #1 then she is able to select one of B,C, or D, and put a value in
>for A. And so on. Of course the person would also possess a number of
>top-level fields like Name, Address, Phone, etc., but those by themselves
>would not be a problem.

>There are different KINDS of entity: People, and Companies. They can have
>relationships with each other (for example, Dr. John Smith, MD, is
>affiliated with Springfield General Hospital). Different kinds of entity
>get different options (Companies would not get the Health Care Worker option
>;-).

>Entity relationships unlock data options: "Dr. John Smith" is related to
>"Springfield General". Because Springfield General has been selected as
>type "Hospital", Dr. Smith's record _now_ allows us to choose his specific
>hospital staff title and watch hours from a list. But those options
>_would_not_ appear if he did not have a relationship with that particular
>type of company.

>That's the basic idea. I apologize for not knowing the proper terminology,
>but I'm fairly new to database programming and this is my first experience
>with this particular problem.

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.

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.

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 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. Received on Wed Jul 16 2003 - 10:03:59 CEST