0

I have a number of distinct items stored in different MySQL tables, which I'd like to put in a tree hierarchy. Using the adjacency list model, I can add a parent_id field to each table and link the tables using a foreign key relationship.

However, I'd like to use a nested sets/modified preorder tree traversal model. The data will be used in a environment that's heavily biased towards reads, and the kind of queries I expect to run favour this approach.

The problem is that all the information I have on nested sets assumes that you only have one type of item, stored in a single table. The ways round this that I can think of are:

  • Having multiple foreign key fields in the tree, one for each table/item type.
  • Storing the name of the item table in the tree structure as well as the item ID.

Both approaches are inelegant to say the least, so is there a better way of doing this?

ChrisW
  • 54,973
  • 13
  • 116
  • 224
  • I can't think of any way to do this other than those you mention. I'd lean towards the second, storing a type discriminator in the tree nodes, then having the item tables refer to the tree nodes. I'd go so far as to use the same primary key for the tree node and the corresponding item, making the PK of the item table also an FK referencing the tree table. This is broadly the approach taken by most object-relational mappers for handling inheritance, FWIW. – Tom Anderson May 15 '10 at 09:44

2 Answers2

1

RDBMS are not a good match to storing hierarchies to begin with, and your use case makes this even worse. I think a little more fine tuned but still ugly variations of your own suggestions are what you are going to get using a RDBMS. IMHO other data models would provide better solutions to your problem, like graph databases or maybe document databases. The article Should you go Beyond Relational Databases? gives a nice introduction to this kind of stuff.

nawroth
  • 4,321
  • 1
  • 24
  • 21
0

You have have several types of tree, and a single table which contains the tree information (i.e. the left/right values) for all tree types?

If you have have several types of tree, why not a different table for each type?

ChrisW
  • 54,973
  • 13
  • 116
  • 224
  • No. One tree, multiple data types. Imagine a table each of countries, cities and shops and cafes. Each table would hold different data, but all items would have a name and ID that could be used in a single tree to represent the position of shops and cafes in a geographical hierarchy. –  Jul 07 '09 at 11:46
  • 1
    I don't see how you hope to `select` data for countries, cities, shops, and cafes in a single select statement, given that the types have different data fields. Or if they don't have different data fields, then they're not different data types and can all be i the same table. – ChrisW Jul 07 '09 at 12:05