Ask the Expert

Hierarchal data storage in MySQL

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?

Requires Free Membership to View

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 first published in May 2007

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
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
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: