Q

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.
This was last published in May 2007

Dig Deeper on Open source databases

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataCenter

SearchServerVirtualization

SearchCloudComputing

SearchEnterpriseDesktop

Close