Root tree / diagram / hierarchy view in database
There seem to be some good options for representing hierarchical data in a database, the most popular seems to be with tree traversal algorithms.
Another option that will probably work in my case is to do it recursively. This could involve storing the parent ID and navigating from there - although this also requires some sort of direction.
Now I have a problem where I have a set of elements that can be characterized by a connection diagram, but there is no root and not necessarily a starting point. For example, it might happen that the elements go around them, so the ordering is only element per element, not complete. Water or not order - "parent" or "child" depends on which direction you start in, so to speak.
In addition, each compound must have several properties, so the compounds must be identified in some way.
Here is an example, but note that this is just a small example, but you can already see that a simple traversal algorithm can start at 254, but then go from 203 to 162, then 254 is actually a child to 162, which can be a problem (I am far from being basic, so I honestly don't know)
Another thing is that I'm limited to Access, which means I'm pretty much limited to your standard SQL commands without recursiveness or functions in SQL.
For example, many SQL algorithms that convert to left / right traversal tree on the fly do not work with Access SQL.
I have a lot of interest in solving this question, regardless of VBA.
In terms of performance, I expect fewer than 5000 items, although queries about item properties and their connections can have dozens of items. The database will be used by fewer than 10 users at a time, although these things tend to expand quickly here if they work well.
So how would you implement this construct?
source to share
I used Joe Selko's nested set approach. It works really well in the right situations. This is not one of these situations.
A more flexible approach, and one that I would recommend you take, is what Bill Carwin calls a table .
The basic idea is that you have one entry for each possible path. Bill suggests two fields: ancestor_id and descendant_id. It is not clear in your diagram if the ancestor / descendant paradigm is applied in your case.
I also find it helpful to add at least one more field for the number of transitions between nodes. I would adapt Bill's method by creating a table with three fields:
- node A
- NodeB
- Hop
Here are some sample data for your chart:
NodeA NodeB Hops
------ ------ ----
tog171 tog171 0
tog171 abb521 1
abb521 tog171 1
tog171 tog226 2
tog226 tog171 2
tog171 tog218 3
tog218 tog171 3
If there are multiple meanings for different colored lines and solid or dotted lines, an additional field that captures the semantic meaning can also be added to your table.
You end up with many records in your table, but the flexibility is almost limitless. And, looking at your diagram, flexibility seems to be your biggest need.
EDIT . This first line in my 0-hop sample data is essentially a technique I learned from PJ Eby in my blog post Simplest (?) Way to Execute Tree Based Queries in SQL . The purpose of these nodes is to make it easier to insert and remove nodes. I highly recommend this page for a detailed overview of the implementation of closure tables.
I think the PJ Eby page is actually the best resource for writing to a closure table, while Bill Karwin's answer has great examples of reading from a table.
source to share