sexta-feira, 23 de julho de 2010

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)
011
01011.1
01021.2
0101011.1.1
022
02012.1
02022.2
0202012.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:

POSICAONODO
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.

2 comentários:

Claudio disse...

Gabriel, que tu achas dessa forma de representar uma hierarquia:

http://articles.sitepoint.com/print/hierarchical-data-database

A insercao e' um pouco mais trabalhosa, o que pode ser um problema para arvores muito grandes, mas fazer um select de uma sub-tree e' trivialmente simples.

[]s
~heckler

forinti disse...

Esse é o método proposto pelo Celko. A inserção é mais trabalhosa mesmo. Acho que a forma que eu mostrei é melhor, porque inserir ou tirar um nodo, em muitos casos, não afeta em nada os outros. E mesmo olhando um único registro já se pode saber onde ele se situa no mundo. E, se não estou enganado, dá para tirar um nodo de um ponto da hierarquia e colocá-lo noutro com dois updates. Estou trabalhando nisso!