Storing large tree structures in a MySQL DB

Best methods

[trillianjedi]

Hi all,

I need to store a tree structure in a DB. It will be far more read from than written to, but still needs to be written to.

It will get quite large (say, 50,000 sub or sub-sub nodes).

I did some research and came across two methods in common use - adjacency list and modified pre-order traversal. The latter, modified pre-order traversal, seems certainly more efficient from a read perspective, but on a large tree updating the right values of all these sub and sub-sub nodes could be time-consuming and slow.

Are there any other methods that I've missed that are suitable for this kind of data hierarchy ?

Many thanks....

[coopster]

I've read references to others but in all reality they are merely variations on the adjacency list model with a column or two added for stacking/recursion purposes. And that is what you are going to have to decide for your application. That said, 50,000 seems an awful large number for categories and subcategories.

[trillianjedi]

Hey coopster.

That said, 50,000 seems an awful large number for categories and subcategories.

Yes. Indicates perhaps we need to re-think the overal information architecture. Trouble is we keep coming back to tree structures as it's the natural "fit" for the type of data being stored.

I'll try and re-think it again - thanks for the input.

[edited by: coopster at 12:36 pm (utc) on Mar. 19, 2009]

[coopster]

for the type of data being stored

Wait a minute, are we talking data or categorization of data?