2 ms·

"Data structures: storing non-flat data (trees, graphs) in a SQL database == FAIL" Can you expand on this a bit? Or is this another MySQL-ism? Databases I use

• yummyfajitas 17y ago
  Some methods of storing trees that I've seen: You can store a tree using foreign keys for the children, but traversing the tree to level m requires m joins. Finding children at an arbitrary level requires O(tree depth) queries. You can use preorder tree traversal which consists of mapping nodes to pairs of numbers representing intervals on the real line. Then "x is descended from y" is equivalent to "x's interval is contained in y's interval", and this at least lets you find the children of x. Going up a tree is harder. Inserting into the tree is tricky as well. Another hack is to store on each node a string containing it's ancestors. I.e., "/2/4/27/35" indicates a node is descended can be reached from the root (node 2) by following the path 2->4->27->35. Then the regex search "2/4/27/35/.*" finds all children of node 35. This one is utter fail. However, I freely admit I'm not a database expert. Is there some non-ugly solution I'm unaware of?