sql - relacionamento - programas similares ao astah




Design de banco de dados para marcação (8)

Como você projetaria um banco de dados para suportar os seguintes recursos de marcação:

  • itens podem ter um grande número de tags
  • as pesquisas por todos os itens marcados com um determinado conjunto de tags devem ser rápidas (os itens devem ter TODAS as tags, por isso é uma pesquisa AND, não OR)
  • criar / escrever itens pode ser mais lento para permitir pesquisa / leitura rápida

Idealmente, a pesquisa de todos os itens marcados com (pelo menos) um conjunto de n tags deve ser feita usando uma única instrução SQL. Como o número de tags a serem pesquisados ​​e o número de tags em qualquer item são desconhecidos e podem ser altos, o uso de JOINs é impraticável.

Alguma ideia?

Obrigado por todas as respostas até agora.

Se não me engano, no entanto, as respostas dadas mostram como fazer uma pesquisa OR em tags. (Selecione todos os itens que tenham uma ou mais de n tags). Eu estou procurando uma eficiente pesquisa AND. (Selecione todos os itens que possuem TODAS as tags n - e possivelmente mais).



Eu daria uma segunda sugestão do @Zizzencs para que você pudesse querer algo que não fosse totalmente (R) centrado no DB

De alguma forma, acredito que o uso de campos nvarchar simples para armazenar essas tags com algum armazenamento em cache / indexação adequado pode render resultados mais rápidos. Mas isso sou só eu.

Eu implementei sistemas de marcação usando 3 tabelas para representar um relacionamento Many-to-Many antes (Item Tags ItemTags), mas eu suponho que você estará lidando com tags em muitos lugares, eu posso dizer que com 3 tabelas tendo que ser manipulado / consultado simultaneamente o tempo todo definitivamente tornará seu código mais complexo.

Você pode querer considerar se a complexidade adicionada vale a pena.


Eu só queria destacar que o artigo que @Jeff Atwood links para ( http://howto.philippkeller.com/2005/04/24/Tags-Database-schemas/ ) é muito completo (discute os méritos de 3 esquemas diferentes abordagens) e tem uma boa solução para as consultas E que normalmente terão um desempenho melhor do que o que foi mencionado aqui até agora (ou seja, ele não usa uma subconsulta correlacionada para cada termo). Também muita coisa boa nos comentários.

ps - A abordagem que todos estão falando aqui é referida como a solução "Toxi" no artigo.


O método mais fácil é criar uma tabela de tags .
Target_Type - caso você esteja marcando várias tabelas
Target - A chave para o registro que está sendo marcado
Tag - o texto de uma tag

Consultar os dados seria algo como:

Select distinct target from tags   
where tag in ([your list of tags to search for here])  
and target_type = [the table you're searching]

ATUALIZAR
Com base em sua exigência para as condições e, a consulta acima se tornaria algo como isto

select target
from (
  select target, count(*) cnt 
  from tags   
  where tag in ([your list of tags to search for here])
    and target_type = [the table you're searching]
)
where cnt = [number of tags being searched]

Parafraseando o que os outros disseram: o truque não está no esquema , está na consulta .

O esquema ingênuo de Entidades / Etiquetas / Tags é o caminho certo a seguir. Mas, como você viu, não está imediatamente claro como realizar uma consulta AND com muitas tags.

A melhor maneira de otimizar essa consulta será dependente da plataforma, então eu recomendo remarcar sua pergunta com o seu RDBS e mudar o título para algo como "A melhor maneira de executar E consultar em um banco de dados de marcação".

Eu tenho algumas sugestões para o MS SQL, mas vou abster-me caso não seja a plataforma que você está usando.



Uma variação da resposta acima é pegar os ids de tag, classificá-los, combinar como uma string separada e misturá-los. Em seguida, simplesmente associe o hash ao item. Cada combinação de tags produz uma nova chave. Para fazer uma pesquisa AND, basta recriar o hash com os IDs de tag e a pesquisa fornecidos. Alterar tags em um item fará com que o hash seja recriado. Itens com o mesmo conjunto de tags compartilham a mesma chave hash.


Você não poderá evitar junções e ainda assim ser um pouco normalizado.

Minha abordagem é ter uma tabela de tags.

 TagId (PK)| TagName (Indexed)

Então, você tem uma coluna TagXREFID na sua tabela de itens.

Esta coluna TagXREFID é um FK para uma terceira tabela, eu chamarei de TagXREF:

 TagXrefID | ItemID | TagId

Então, para obter todas as tags de um item seria algo como:

SELECT Tags.TagId,Tags.TagName 
     FROM Tags,TagXref 
     WHERE TagXref.TagId = Tags.TagId 
         AND TagXref.ItemID = @ItemID

E para obter todos os itens para uma tag, eu usaria algo assim:

SELECT * FROM Items, TagXref
     WHERE TagXref.TagId IN 
          ( SELECT Tags.TagId FROM Tags
                WHERE Tags.TagName = @TagName; )
     AND Items.ItemId = TagXref.ItemId;

Para AND um monte de tags juntos, você iria modificar ligeiramente a declaração acima para adicionar AND Tags.TagName = @ TagName1 AND Tags.TagName = @ TagName2 etc ... e construir dinamicamente a consulta.





tagging