My question is on hierarchal data storage in MySQL. I want to store a hierarchy in which a node can have more than one parent on more than one level. The algorithm isn't very hard -- all paths are finite. But how do I store this? Maybe a table, in addition to my tree table, defining all paths?
The common table designs for trees (adjacency lists, nested sets and materialized paths) assume a single parent for each node. Your suggestion of splitting the materialized path to a separate table seems like a reasonable solution. Navigation through the tree will most likely require a recursive approach, which is not practical within MySQL; you'll need to implement that in a separate application.
Dig Deeper on Linux servers
Get suggestions for choosing MySQL character sets and field collations from expert Scott Noyes.
A MySQL expert describes two ways that the multi-master support can be used.
An expert describes where to find information on detection deadlock algorithms in MySQL.