Handling hierarchical object relationships

edwinsn · 2011-03-28 19:06:28

Hi Arnaud,

What approach do you recommend to persist tree-like object relationships? Currently with direct access to sqlite I use only two fields to store the relationships: "ParentId" and "Idx", where "ParentId" references a rowid of the parent nodes, while "Idx" indicates the index of a node among its siblings.

Thanks. I know I've got too many questions in this forum, I'm just new in the ORM thing

ab · 2011-03-29 05:36:37

You could do this that way.
But when you are dealing with a data structure, always ask you "what for" instead of "how".

That means, you should first ask yourself the actions which will be performed to the tree.
You'll need:
- to insert nodes;
- to delete nodes;
- to find a node;
- to know about nodes hierarchy.

Using an Idx is perhaps not the better: if you delete or insert a node, you'll have to update the index value of all its siblings.
So you could use a NextSibID (and a PrevSibID) field instead.
And don't forget to create an index on ParentID + NextSibID, in order to speed up the search of a child.

But all this is a little difficult to implement.
We would need perhaps a generic class for handling this, with generic methods like "GetNextNode" "Parent" "Children".

PS:
I'd perhaps tend just to use a row list of objects, then store the hierarchy in a separate record containing Level/ID pairs, perhaps just like a dynamic array.

type
  TSQLNode = record
     Level: integer;
     ID: integer;
  end;
  TSQLTree = array of TSQLNode;

But I didn't have to use such relationships with our ORM yet.
So I'm investigating just as you are.

edwinsn · 2011-03-29 06:34:43

Thanks Arnaud, in the model layer I use a object model that uses properties such as Parent, PrevSibling, NextSibling, InsertChild, DeleteChild, etc, and these members are necessary when implement the program logic, just like a generic tree data structure.

But here we are taking about persisting the objects. When saving to the database I use "ParentId" and "Idx" table fields instead, to simply things.

mORMot Open Source

#1 2011-03-28 19:06:28

Handling hierarchical object relationships

#2 2011-03-29 05:36:37

Re: Handling hierarchical object relationships

#3 2011-03-29 06:34:43

Re: Handling hierarchical object relationships

Board footer