Problem solve Get help with specific problems with your technologies, process and projects.

Hierarchal data storage in MySQL

An expert describes how to perform store a hierarchy in MySQL by splitting the materialized path to a separate table.

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

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.