One thought on “The Scarecrow

  1. you’ve studied graph terohy in computer science classes you’ll remember that the order of child nodes in a tree is irrelevant to the graph. That is, (R (F) (M) (L)) is the same tree as (R (M) (L) (F)).So keep in mind that display order data properly should be separate from the hierarchy data.But since path enumeration is essentially a denormalized solution already, perhaps we can bend the rules.Therefore you could just make sure to update the primary key values of tree nodes, so that the natural order of these values matches your desired display order.This is admittedly a fragile solution, and pretty much a hack of coupling the sibling order to the primary key values (which should be arbitrary and independent of meaning).This illustrates that each solution to represent trees in SQL has its strengths and weaknesses, and therefore which design you use should be based on the types of queries you need in a given application.It would take a whole book to compare how to implement every scenario in all the designs of trees. In my book “SQL Antipatterns,” I had to limit myself to 20 pages on hierarchical data, because there are other topics to cover.

Leave a Reply