how to represent trees and their content in MySQL?

Question

I need to be able to store something like this:

where the green is a template type, the gray is a field container type, and the white is field. (That being, a field has a label and some text, both mediumtext for simplicity; and a field container can store other field containers or text, and a template is a top-level field.)

Now, let's say I want to allow users to create any number of these structures, but it is unlikely to be more than say 10. And, of course, we need to be able to link data to it.

This is all to be able to store in a database an associative array that looks for the above like, in pseudo code:

my_template = {
  page_info => { description => 'hello!!!' },
  id => 0,
  content => { background_url => '12121.jpg', text => ...
}

Having an easy way to add a field to all data using the template when the template changes (say, we add a keywords to page_info) would be a big plus.

I can't figure this out at all, thanks a lot!

Answer 1

There are several different ways to store heirarchical data structures (trees) in MySQL. You can for example choose:

• Adjacency list
• Nested sets
• Path enumeration
• Closure table

See Bill Karwin's presentation for more details on the pros and cons of each.