Which kind of hierarchical model (tree) for records from tables with different attributes ?

Hi,

This topic is not specific to MODx, but could interest those using MODx as a framework.

I have a catalog of hard drives using five tables with this hierarchy :

categories > series > subseries > models > items

I want to represent it as a tree and wonder about the best model to store the data in this case.
There should not be frequent moves in the nodes. However, the possibility doing it as well as thus of inserting/deleting nodes is important.

All records in the miscellaneous tables have "id" and "parent" attributes, in the spirit of the adjacency list model, like the way MODx documents are hierarchied.

In order to avoid redundancy of infomation at the item level, each table has custom attributes.
For instance, the 2.5 inch format of all Momentus drives is stored in the "series" table.
The last level "items" stores things that cannot be stored elsewhere like the serial number.

So, basically things can be thought as object oriented with a basic class "Node" being extended:
class Node {id, parent}
class Serie extends Node {format}
class Model extends Node {capacity, speed, cache}
class Item extends Node {serial}

A category can have another category as parent.
A subserie can have another subserie as parent.
There is no hierarchical level limit to the number of subseries.
The drive models can be attached to either series or to subseries.
The items are always attached to drive models.

I heard about the Nested Set Model and its benefits over the Adjacency List Model (http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/)
and also its drawbacks (https://robsite.net/nested-sets-suck-on-how-not-to-map-trees-into-a-relational-database/).

Concerning the Adjacency List Model, I appreciate the fact that it is simple to create SQL insert requests from a text file.
The software that I'm currently using stores both the "parent" of each node, as well as the full path from the root node in a chained listed of identifiers "eg. 20.36.81.83".
I assume this is for travelling the tree more efficiently. It seems using a B-Tree.

How to manage extended attributes (others than id, parent, name) ?
Should I store all the tree hierarchy in a table, have a column to store which kind a node belongs to and retrieve extended attributes using JOIN ?

Thank you for your ideas and advices.

Tree algorithms depend on the requirements of your particular use case (e.g., potential number of entries, how important is speed, caching issues, etc.) so it's difficult to give advice.

I would be tempted to use xPDOSimpleObject as the parent of all objects so you can use $modx->getObjectGraph() to retrieve the objects and their associated extended attributes in one query and have the results cached.

Being lazy, I would also be tempted to use or extend modResource, and repurpose the modResource fields to store everything with a translation array for the fields. That way you could use Wayfinder to produce your tree (and have it cached). It would be slower, but might still perform well enough and it would be a lot quicker and easier to implement.

Using modResource, it might be possible to use NewsPublisher for the input form.

The Collections extra might also be useful.