Hierarquias legíveis no SQL

A maneira mais comum de modelar hierarquias em tabelas é usar uma coluna para referenciar a própria tabela. Isso funciona muito bem no Oracle, porque a sintaxe tem umas facilidades para tratar esse tipo de construção. No entanto, não creio que seja a melhor maneira de resolver esse problema.

Joe Celko difundiu largamente a solução dos conjuntos aninhados. Eles já são um grande avanço, mas ainda não resolvem o problema que eu quero resolver: tornar os dados mais legíveis, sem precisar usar consultas complicadas. Eu queria executar um simples select com order by e pronto.

Uma maneira simples de fazer isso é colocar a hierarquia num texto usando uma notação posicional. Por exemplo, para representar um nodo "3.2.4" usa-se um texto "030204". O pai desse nodo (3.2) seria o "0302". Usando apenas números, tem-se 99 elementos por nível. Com o alfabeto, pode-se mais. Para todas as aplicações que vi até hoje, basta.

Criei uma tabela de testes assim (a chave é a coluna POSICAO):

Posição (POSICAO)	Nome (NOME)
01	1
0101	1.1
0102	1.2
010101	1.1.1
02	2
0201	2.1
0202	2.2
020201	2.2.1

Para enumerar a hierarquia toda de uma única vez, basta a seguinte consulta:

select posicao, nome
from hierarquia
order by 1

Dá até mesmo para usar a sintaxe especial do Oracle e com isso, achatar a hierarquia e mostrar em cada registro todos os ascendentes:

select posicao, SYS_CONNECT_BY_PATH(nome,'>') nodo
from hierarquia
 connect by  prior posicao = substr(posicao,1,length(posicao)-2)
 start with length(posicao)=2
order siblings by posicao

Isso vai mostrar algo assim:

POSICAO	NODO
01	>1
0101	>1>1.1
010101	>1>1.1>1.1.1
0102	>1>1.2
02	>2
0201	>2>2.1
0202	>2>2.2
020201	>2>2.2>2.2.1

Não é preciso completar os espaços vazios com zeros; isso dificulta muito as consultas. Por exemplo, na primeira tentativa, usei "010000" para o nível 1. Os zeros adicionais não só não ajudam, como tornam a consulta anterior muito mais complicada; sem eles, basta tirar dois caracteres do fim para achar o nodo pai; com eles, é preciso encontrar onde os pares de zeros terminam (ou começam, para quem vem da esquerda).

Se for preciso representar mais nodos por nível, basta usar mais posições. Com 3 por nível, já são possíveis 999 nodos. E para tornar a hierarquia mais alta, basta ir adicionando caracteres .

Esse tipo de construção, além de tornar os dados mais legíveis, facilita sobremaneira algumas pesquisas. Por exemplo, encontrar todos os nodos de terceiro nível com uma tabela auto-referenciada seria muito mais complicado.