JohnCand's questions

Vamos supor que você tenha uma nodestabela como esta:

CREATE TABLE nodes (
    node serial PRIMARY KEY,
    parent integer NULL REFERENCES nodes(node),
    ts timestamp NOT NULL DEFAULT now()
);

Ele representa uma estrutura de árvore semelhante a um nó padrão com nós raiz no topo e vários nós filhos pendurados nos nós raiz ou outros nós filhos.

Vamos inserir alguns valores de exemplo:

INSERT INTO nodes (parent) VALUES
  (NULL), (NULL), (NULL), (NULL)
, (1), (1), (1), (1), (6), (1), (6)
, (9), (6), (6), (3), (3), (3), (15);

Agora eu quero recuperar os primeiros 10 nós raiz e todos os seus filhos até uma profundidade de 4:

WITH RECURSIVE node_rec AS (
    (SELECT 1 AS depth, * FROM nodes WHERE parent IS NULL LIMIT 10)

    UNION ALL

    SELECT depth + 1, n.*
    FROM nodes AS n JOIN node_rec ON (n.parent = node_rec.node)
    WHERE depth < 4
)
SELECT * FROM node_rec;

Isso funciona muito bem e me dá o seguinte resultado:

 depth | node | parent 
-------+------+--------
     1 |  1   |
     1 |  2   |
     1 |  3   |
     1 |  4   |
     2 |  5   |  1
     2 |  6   |  1
     2 |  7   |  1
     2 |  8   |  1
     2 | 10   |  1
     2 | 15   |  3
     2 | 16   |  3
     2 | 17   |  3
     3 |  9   |  6
     3 | 11   |  6
     3 | 13   |  6
     3 | 14   |  6
     3 | 18   | 15
     4 | 12   |  9

Como você deve ter notado, não há ORDER BYcláusula, então a ordem não é definida. A ordem que você vê aqui é de nós raiz para nós mais profundos.

Como eu ordenaria os resultados como eles apareceriam em uma exibição em árvore expandida, como você pode ver na imagem de exemplo abaixo?

Basicamente, quero que os nós filhos sejam colocados logo após o nó pai correspondente. Se dois ou mais nós filhos tiverem o mesmo nó pai, quero que eles sejam classificados por seu carimbo de data/hora. Com base no exemplo acima, aqui está a ordem de saída desejada que estou tentando alcançar:

 depth | node | parent | ts
-------+------+--------+---------
     1 |  1   |        | 2014-01-01 00:00:00
     2 |  5   |     1  | 2014-01-01 00:10:00
     2 |  6   |     1  | 2014-01-01 00:20:00
     3 |  9   |     6  | 2014-01-01 00:25:00
     4 |  12  |     9  | 2014-01-01 00:27:00
     3 |  11  |     6  | 2014-01-01 00:26:00
     3 |  13  |     6  | 2014-01-01 00:30:00
     3 |  14  |     6  | 2014-01-01 00:36:00
     2 |  7   |     1  | 2014-01-01 00:21:00
     2 |  8   |     1  | 2014-01-01 00:22:00
     2 |  10  |     1  | 2014-01-01 00:23:00
     1 |  2   |        | 2014-01-01 00:08:00
     1 |  3   |        | 2014-01-01 00:09:00
     2 |  15  |     3  | 2014-01-01 10:00:00
     3 |  18  |     15 | 2014-01-01 11:05:00
     2 |  16  |     3  | 2014-01-01 11:00:00
     2 |  17  |     3  | 2014-01-01 12:00:00
     1 |  4   |        | 2014-01-01 00:10:00

Vamos supor que você tenha uma tabela groupse uma tabela item. Cada item pertence a exatamente um grupo. É uma parte inerente desse grupo. Um item não pode existir fora de um grupo e não pode ser movido para outro grupo.

Ao tentar decidir sobre uma chave primária para a itemtabela, o que devo usar?

Devo criar uma chave serial global artificial como esta:

CREATE TABLE items
(
    item serial PRIMARY KEY,
    group integer NOT NULL REFERENCES groups(group),
);

... ou devo usar uma chave composta e uma série de itens por grupo como esta:

CREATE TABLE items
(
    group integer NOT NULL REFERENCES groups(group),
    item integer NOT NULL,

    PRIMARY KEY(group, item)
);

A razão pela qual estou mais inclinado para a segunda solução é que o URL da postagem sempre mostrará o grupo e o item, portanto, faz sentido ter os dois como a chave primária composta. No caso da primeira solução, a URL contém informações supérfluas porque o ID do grupo já pode ser deduzido apenas do ID do item. A estrutura da URL é fornecida, no entanto, e não pode ser alterada.

A desvantagem da segunda solução é que você precisa gerenciar uma série por grupo (ou seja, cada número inteiro de item deve começar em 0 para cada grupo).

O que é melhor em termos de melhores práticas, normalização e desempenho? Ou é simplesmente uma questão de gosto?