JustinTArthur edited this page Jun 21, 2012 · 8 revisions
Clone this wiki locally

For any table in an RDBMS that holds items and their children (and their children, etc.), there are several ways of storing the parent and child relationships in the structure.

Most primitively, the table can have a foreign key column pointing to a row's parent row in the same table. However, to traverse this structure at runtime, you'd have to employ recursion. Recursion isn't a bad thing, but because SQL doesn't support it (it's not Turing-complete), you'd have to traverse the tree one level at a time between the app and DB...

Feature Django Rails Java EE
Nested Set Library: django-mptt Awesome Nested Set JPA-NestedSet
Depth column

makes certain queries easier/faster, makes HTML display of a tree super-easy)

Yes Yes, but not properly populated during .rebuild! No. There are some fancy queries you can use to achieve a depth calculation, but they can be expensive.
Parent Foreign Key Column

Makes some queries faster, acts as a fail-safe if MPTT sturecture gets corrupted.

Yes Yes Not managed by library, but can be managed with JPA separately
Multiple Roots/Trees Yes. It's the default behavior. Maybe via :scope option, but poorly documented Yes, but poorly documented.
Custom Insertion Order

Useful to avoid having to resort query results recursively before presenting

Yes, using a list of columns on the same table. No No, but I'm working on it.