Recursive joins/Hierarchical data (all)

Topic

New Reply

Dear members of the lounge,

do you have any good links/articles/tips/guides about the use of self/recursive joins in databases?

I found plenty Access or other database development books mentioning this issue, but never any thorough reading. From my experience, the main issue should be how one can integrate hierarchical data in a relational database. This has many aspects:
– table & relational structure: self joins = quite simple;
– query & lookup list structures, forms, reports = more complicated, especially if there is an unlimited or unknown number of ‘levels’;
– fluently managing the data: adding new data, changing the ‘tree structure’,…
– the possibility to let children inherit their parent’s properties/data unless their are own properties/data entered
– …

Some guidelines & examples here should be quite helpful. I ask this question as in several databases I worked on (semi-professional, as many, probably), such data re-appeared:
– contacts & addresses of people in big companies & government administrations
– a database to manage the ‘family tree’ of my ancesters (though, there must be some useful examples of this…)
– a library where storage place of documents (rooms/closets/shelves/…) could be (known) in a variable level of detail.
– …

In case you find this question too theoretically & vague, I accept this humbly & probably once will return with a more practical question. Otherwise, if you can help me, thanks a lot!

Hans

ps A merry christmas & a happy new year!
(With more peace, development & human rights in the world for everybody)

Reply | Quote

Replies

could you explain what you mean by this:[indent]

---

– fluently managing the data: adding new data, changing the ‘tree structure’,…
– the possibility to let children inherit their parent’s properties/data unless their are own properties/data entered

---

[/indent] There is no inheritence in Access except what you build in forms or through code or stored procedures (SQL Server backend). Recursive records are still independent of each other, they simply contain a link that allows you to “climb” or “descend” the tree through that link. As far as changing the “tree structure” goes, Are you talking about nesting or something else? The “structure”, as I interpret it, is determined by the field involved and the nature of the self-join.

Reply | Quote

Charlotte,
I write more after Christmas (must leave for family reunion…) but already thanks for your attention.
Generally, I thought there could be some thumb rules/ways/… to construct forms in such a way that the two points you asked more information about could be handled as user friendly as possible. I’ll try to catch up later.
Hans

Reply | Quote

An example…
Consider a multimedia library database. ‘Documents’ include maps, books, magazines, articles, CD-ROM’s, files stored on network locations,…
One property is ‘location’ (both for digital copy as hard copy). The locations are ‘hierarchical’:
– site: head quarters, lab,…
– room: office1, office2, office3, cellar room 1, cellar room 2
– furniture: book closet 1, book closet 2, desk1, desk2
(- optional: shelf,…)
I would like to offer the user the choice to specify the hard copy location as much as possible, for example because the specific location still might be unknown at the time of data entry.
I propose a self joined tblLocations = lc_IDlocation + lc_Name (+ lc_Level) + lc_IDParendLocation.
Now I’ll probably find some solution, but I still wonder if there is no more methodical way for e.g. presenting all these locations (at all levels) available for entry through a combobox, or constructing a form for easy location data management,…
I realize that solutions can be quite case specific. On the other hand, I dealt in a couple of other situations with similar problems, so I started wondering whether there aren’t any white papers/articles describing how one should efficiently & methodically manage such data.
I hope that this example explains a bit better what I meant…
Hasse

Reply | Quote