Readers Feedback: Database Documentation (ERD)
Aaron Writes:
I work for a university and our main student information system does not have a database diagram. The database has close to 900 tables and I have been asking the vendor for a full database diagram for years. The main reason I would find it useful is for reporting purposes.
Alexander Writes:
I work in the UK on a very large healthcare system. There is absolutely no way that any technical person starting on this programme would every be able to understand how the system is put together without the existence of a maintained ERD and data dictionary. There are just too many functional areas in our application and it evolves from release to release.
However, as you have pointed out many don’t see the value that an ERD and data dictionary can bring to a project. More importantly most organisations don’t see that maintaining these objects is worth the expense and I can sympathise with that logic. Over the years I have worked on projects that have and have not had ERD or data dictionaries and what I have found is that it takes longer to understand the systems because the knowledge is in silos. In my view having this information and publishing is not ‘old school’ but is aligned with the philosophy of open access to information.
Todays Topic – Database Hierarchy Data Structures
Today we’re going to look at a hierarchy data structure that is a lot easier to consume than a simple linked list. This method is called the Nested Set Hierarchy Model. It allows all nodes within a hierarchy to have a direct relationship to every other node, without having to walk the linked list chain.
The hierarchy table is modified, replacing the ParentHierarchyId with three values defining the relationship of nodes to one another. Now you have three new columns: 1) LeftId, 2) RightID and 3) Level.
So what is the relationship between records in this table? It’s probably easier to demonstrate the relationships with a few records, where you can see the pattern implemented here.
| HierarchyId | LeftId | RightId | Level | Name |
| 1 | 1 | 16 | 0 | C: |
| 2 | 2 | 16 | 1 | Data |
| 3 | 3 | 9 | 2 | Documents |
| 4 | 4 | 6 | 3 | Excel |
| 5 | 5 | 5 | 4 | Budget.xls |
| 6 | 6 | 6 | 4 | Goals.xls |
| 7 | 7 | 9 | 3 | Word |
| 8 | 8 | 8 | 4 | Resume.doc |
| 9 | 9 | 9 | 4 | References.doc |
| 10 | 10 | 13 | 2 | Music |
| 11 | 11 | 13 | 3 | Albums |
| 12 | 12 | 12 | 4 | Journey to the Center of the Earth |
| 13 | 13 | 13 | 4 | The Master and the Musician |
| 14 | 14 | 16 | 2 | Videos |
| 15 | 15 | 15 | 3 | Firefly |
| 16 | 16 | 16 | 3 | Monument |
To find all of the descendants of any node, it is simple as finding all the records with a LeftId value between the node’s LeftId and RightId
If you want only children of any node you can find all the records with a LeftId value between the node’s LeftId and RightId, and a Level = the level from the selected node + 1.
If you retrieve all the nodes sorted by LeftId, you can easily build a hierarchy data object, walking through each of the records, and assigning a node to the appropriate parent.
The downside to this model is that the LeftId and RightId values have to b e re-set when you introduce new data into the middle of an existing hierarchy. However, most hierarchies are fairly static, so rebalancing isn’t a frequent occurrence. So, data entry may be a little slower, but data retrieval is highly optimized.
You can see a number of different kinds of queries if you check out this Article on Wikipedia.
Cheers,
Ben