I have a class that looks something like this:
class Entity
{
public string Id { get; set; }
public List<Entity> Children { get; set; }
public void DoStuff(Data data)
{
// stuff
foreach (var child in Children)
{
child.DoStuff(data);
}
}
}
And I need to persist this in a database. Available options: MongoDB, SQL server or PostgreSQL.
I was wondering what is the best approach for storing this kind of tree data considering these conditions:
- Read performance is most important when the whole tree is queried - parent with all of its descendants.
- Usually only one child will be present per
Entity
in theChildren
list. But the depth of the tree could be 50-100. - Once the parent is created I need to be able to add/delete children. But the speed of this operation is not that important.
In JSON data looks like this:
{
"id": "xyz_0",
"children": [
{
"id": "xyz_1",
"children": [
{
"id": "xyz_2",
"children": [
]
},
{
"id": "xyz_3",
"children": [
]
}
]
}
]
}
At the moment I'm using MongoDB and storing a single tree per JSON document. And the application code looks something like this:
var entity = mongoCollection
.Find(Builders<Entity>
.Filter
.Eq(o => o.Id, parentId))
.SingleOrDefault();
foreach (var data in GetData(10000))
{
entity.DoStuff(data);
}
But reading a couple of previous posts here and here mentions that it's not such a great idea to store big documents. So I'm looking for alternatives.
1 -> 1.1 -> 1.1.1
...1 -> 1.9 -> 1.9.9
...select * from table where sort_key >= 1 and sort_key < 2
? – svidgen Dec 16 '20 at 16:08child_min
andchild_max
. You set the fields such that thenumber
s of all children are between their parent'schild_min
andchild_max
. To query a (sub)-tree you doSELECT * FROM entity parent JOIN entity child ON child.number BETWEEN parent.child_min AND parent.child_max
. You have to build the actual tree structure in-memory then, but that's certainly quick with only 100-200 entities in a tree. – marstato Dec 16 '20 at 16:12