0

I would like to store information on nodes in lots of different trees in a database.

To begin with there will be over 20000 nodes shared between 500 trees, each node will have 5 number attributes. once constructed each node needs reference to all it's immediate children and no other nodes.

I require to build all trees in memory at initialization time and update/add nodes once the program enters downtime (perhaps every hour or so, although the more the better).

I have looked at the sql adjacency model which seems like it would take too long to construct each table (having to make too many db calls), the nested set model which is a possibility but is more complex to expand the tree which is something which will happen a lot and it increases database complexity for what I see as could be a very basic structure and query set.

I have also looked in to MongoDb but it seems more geared towards JSON type objects and I am using java, and might be over kill, and also HBase which definetly looks over kill (the advantage being if the amount of nodes becomes huge it might come in useful which is a possibility for the future and I could increase write time to the DB which would be an advantage too)

Anyone have any suggestions of how I might go about this?

Are the NoSql dbs overkill? are they much better at storing tree structures? is it poor practice to use them along side sql databases?

rogermushroom
  • 5,486
  • 4
  • 42
  • 68

2 Answers2

1

If you drop the (rgt - lft - 1) / 2 yields number of children property on nested sets, and use floats for the lft/rgt columns, you can insert/update/delete nodes in minimal time.

The main issue when doing so, is to avoid precision-related issues. You can work around the latter by casting to lft/rgt to numeric and back to float, so as to obtain their canonical representation. Example with Postgres:

select (.1::float + .7::float) * 10::float;                          -- 8
select floor((.1::float + .7::float) * 10::float);                   -- 7
select floor(((.1::float + .7::float) * 10::float)::numeric::float); -- 8

The other issue is reasonably easy to manage and occurs when you run out of space: you then occasionally need to re-index part or all of tree -- it requires locking the tree but it's fast enough that you can do so without impacting normal operations.

Denis de Bernardy
  • 75,850
  • 13
  • 131
  • 154
1

if you're using SQL Server 2008+ you can use the new HierarchyID data type meant for such scenarios.

Mladen Prajdic
  • 15,457
  • 2
  • 43
  • 51