Heirarchical Data Structures. MS Access. Together?

Hi, there, all. I have been working on this problem of heirarchical data structures for a while now. I'm trying to implement such a structure in an MS Access environment, which seems to be pretty tricky. Although I have worked out a solution, it's rather complex and not very satisfying and I was hoping there was some simple way that I'm missing. So I thought I would ask and learn.

(Please understand that although I'm an experienced programmer, this is my first foray into database programming. I've only worked with MS Access so far, although I'm happy to learn about other software!)

So I'm looking for suggestions and ideas about:

1. General info about heirarchies in databases. Is there db software specifically designed for this kind of thing? From reading a few posts I know that there are different kinds of heirarchies for different purposes.
2. How to attack the heirarchy problem with Access. This is my current environment because of the requirements of the customer. How might I organize my tables and queries? How would I correspondingly design my forms?
3. What do you think I ought to know, or ask, that I'm not asking about?

Some people may have a different idea than me as to what "heirarchical data structure" means, so I thought I would give a general idea of my situation below so we're all on the same page.

In my heirarchy, the data associated with a person or company (entity) is arranged in layers so that an entity can only possess data on a lower level if they already have a corresponding data element at a higher level. There can be no limit to the number (depth) of levels or the number of data entries on each level. (Think of this as a mailing list/info list where I need to store specific information about people and companies and their relationships with each other.) In addition, connections between entities also allow access to new data options.

This is further complicated by the fact that data can be boolean or string or other, and that I must be able to filter by different data entries. Most importantly, I want users to be able to add data entries to the heirarchy as easily as one would add fields to a database (that's essentially what they are).

For example:

1. Health Care Worker
2. # of Patients per week
3. Doctor?
4. Nurse?
5. Assistant?
6. NASA Employee
7. Astronaut?
8. Engineer?
9. PR Expert?
10. Amount of time spent in space
11. Boy Scouts
12. Cub Scout?
13. Boy Scout?
14. Eagle?
15. Den Mother?
16. 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.

Thanks again for all input!

Jace Received on Wed Jul 16 2003 - 00:53:08 CDT