Hi,
I'm using table with parent_id to themselve and WITH RECURSIVE in SELECT on about 3thousands records.
The "tree" of data is wide (each node has more children) but not deep (maximal depth of branch is 10 nodes).
I'm planning to use same schema on much deeper but narrower tree (most of nodes will have only one child, only few
nodeswill have two or little bit more childs).
It will represent points on map of line construction with many points (nodes). It would have thousands of nodes, there
willbe more independent trees (up to hundreds), some of them will be much smaller then others. Count of nodes in table
willbe about few hundreds of thousands.
Alternatively I can divide line constructions to many sections (from cross of lines to other) and have it on separate
tablelike this:
CREATE TABLE sections (
id_section SERIAL PRIMARY KEY
);
CREATE TABLE section_nodes (
id_node SERIAL PRIMARY KEY,
sections_id_section INTEGER REFERENCES sections (id_section),
x INTEGER,
y INTEGER,
sortid INTEGER -- serial number of onde in one section
);
Solution with recursive is nicer and easier for administration (and SELECTing from it), but won't be problem with
performanceon so many recursion? Is there some limitations of recursive tables?
Or is better solution the second one with seperated sections?
Thanks for help and your opinion.
Dusan