Come progettare una tabella MySql per una nuvola di tag?




database-design tag-cloud (2)

Ho articoli sul mio sito e vorrei aggiungere tag che descrivano ogni articolo, ma ho problemi con la tabella mysql di design per i tag. Ho due idee:

  1. ogni articolo avrebbe "tag" di campo, e i tag sarebbero in formato: "tag1, tag2, tag3"
  2. crea un'altra tabella denominata tag con campi: tag_name, article_id

Quindi quando voglio tag per articolo con ID 1, vorrei correre

SELECT ... FROM tags WHERE `article_id`=1;

Ma vorrei anche conoscere 3 articoli più simili confrontando i tag, quindi se ho un articolo con tag "php, mysql, erlang" e 5 articoli con tag: "php, mysql", "erlang, ruby", "php erlang", "mysql, erlang, javascript", sceglierei 1., 3. e 4., poiché questi 3 hanno la maggior parte dei tag stessi con l'articolo principale.

Un'altra domanda, qual è il modo migliore per ottenere 10 "tag più utilizzati"?


In generale, per questo tipo di relazione molti-a-molti, ci sono tre tabelle:

  • La tabella " article "
    • chiave primaria = id
  • La tabella " tag "
    • chiave primaria = id
    • contiene i dati di ogni tag:
      • nome, per esempio
  • Una tabella " tags_articles ", che funge da tabella join e contiene solo:
    • id_article : chiave esterna che punta a un articolo
    • id_tag : chiave esterna che punta a un tag


In questo modo, non c'è alcuna duplicazione dei dati di ogni tag: per ogni tag, c'è una riga, e solo una, nella tabella dei tag .

E, per ogni articolo, puoi avere diversi tag (cioè più righe nella tabella tags_articles ); e, ovviamente, per ogni tag, puoi avere diversi articoli.

Ottenere una lista di tag per un articolo, con questa idea, è questione di una query aggiuntiva, come:

select tag.*
from tag
    inner join tags_articles on tag.id = tags_articles.id_tag
where tags_articles.id_article = 123


Ottenere i tre articoli "più simili" significherebbe:

  • seleziona gli articoli con i tag del primo articolo
  • usa solo quelli che hanno il numero più importante di tag identici

Non testato, ma un'idea potrebbe essere qualcosa che assomiglierebbe a questo:

select article.id, count(*) as nb_identical_tags
from article
    inner join tags_articles on tags_articles.id_article = article.id
    inner join tag on tag.id = tags_articles.id_tag
where tag.name in ('php', 'mysql', 'erlang')
      and article.id <> 123
group by article.id
order by count(*) desc
limit 3

Fondamentalmente, tu:

  • seleziona gli ID articoli per ogni tag presente nel tuo articolo iniziale
    • poiché c'è un join interno, se un articolo nel DB ha 2 tag che corrispondono alla clausola where, senza la clausola group by , ci sarebbero due righe per quell'articolo
    • ovviamente, non vuoi ri-selezionare l'articolo che hai già - il che significa che deve essere escluso.
  • ma, mentre usi group by article.id , ci sarà solo una riga per articolo
    • ma potrai usare count , per scoprire quanti tag ha in comune ogni articolo con quello iniziale
  • quindi, si tratta solo di ordinare per numero di tag e ottenere solo le terze tre righe.

Prima di tutto, ti consigliamo di utilizzare il suggerimento di Pascal MARTIN sul design del tavolo.

Per quanto riguarda la ricerca di articoli simili, ecco qualcosa per iniziare. Dato che @article_id è l'articolo per cui trovare le corrispondenze, e @ tag1, @ tag2, @ tag3 sono i tag per quell'articolo:

SELECT article_id, count(*)
FROM tags_articles
WHERE article_id <> @article_id
AND tag_id IN (@tag1, @tag2, @tag3)
GROUP BY article_id
ORDER BY count(*) DESC
LIMIT 3




tag-cloud