mORMot Open Source

edwinsn

Member

Registered: 2010-07-02

Posts: 1,215

Handling hierarchical object relationships

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

---

Delphi XE4 Pro on Windows 7 64bit.
Lazarus trunk built with fpcupdelux on Windows with cross-compile for Linux 64bit.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,240

Website

Re: Handling hierarchical object relationships

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

Member

Registered: 2010-07-02

Posts: 1,215

Re: Handling hierarchical object relationships

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.

---

Delphi XE4 Pro on Windows 7 64bit.
Lazarus trunk built with fpcupdelux on Windows with cross-compile for Linux 64bit.