sets

Recently, I saw people migrating from Awesome Nested Set to Ancestry. The reasons are simple – Ancestry is very simple, it just need a new string field in your table, and it’s easier to reason about. So, why should I even consider to use an alternative?

The answer is simple, and I’m going to quote H. L. Mencken: For every complex problem there is an answer that is clear, simple, and wrong.

And the reason that using Ancestry is a bad idea is simple: it doesn’t reflect good design. It treats a single field as a multi-valored column that keeps the ancestry of your object. This means that there’s no way to fetch all records AND their parents in a single query, or fetch all records AND their children, because we need to split the string (ruby-side) and then create a query (also ruby-side). So, in this post, I’ll show some tricks of what we can do with Awesome Nested Set, or even beter, how does it implements the tree pattern (and why it calls it a “set”, not a “tree”).

First, when we think about categories and sub-categories, we think like this:

But this is, in fact, a terrible way of representing trees in SQL. So, the alternative, is to transform it in a group of sets: Parent 1 (P1, for short), is a super-set containing subsets C1 and C2 (Children 1 and Children 2, for short). Each of the children have its own grandchildren, so C1 is a super-set containing G1, and C2 is a super-set containing G2 and G3.

(more…)