¿Cómo diseñar una tabla MySql para una nube de etiquetas?

database-design tag-cloud

Question

Tengo artículos en mi sitio, y me gustaría agregar etiquetas que describan cada artículo, pero estoy teniendo problemas con la tabla mysql de diseño para las etiquetas. Tengo dos ideas:

1. cada artículo tendría "etiquetas" de campo, y las etiquetas estarían en formato: "tag1, tag2, tag3"
2. crear otra tabla llamada etiquetas con campos: tag_name, article_id

Entonces, cuando quiera etiquetas para el artículo con ID 1, correría

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

Pero también me gustaría saber 3 artículos más similares comparando etiquetas, así que si tengo un artículo que tenga las etiquetas "php, mysql, erlang" y 5 artículos con etiquetas: "php, mysql", "erlang, ruby", "php erlang", "mysql, erlang, javascript", elegiría 1., 3. y 4., ya que esos 3 tienen la mayoría de las mismas etiquetas con el artículo principal.

También otra pregunta, ¿cuál es la mejor manera de obtener 10 "etiquetas más usadas"?

Answer 1

En general, para este tipo de relación muchos a muchos, hay tres tablas:

• La tabla de " article "
  • clave principal = id
• La tabla de " tag "
  • clave principal = id
  • contiene los datos de cada etiqueta:
    • nombre, por ejemplo
• Una tabla " tags_articles ", que actúa como una tabla de unión, y contiene solo:
  • id_article : clave externa que apunta a un artículo
  • id_tag : clave externa que apunta a una etiqueta

De esta forma, no hay duplicación de los datos de ninguna etiqueta: para cada etiqueta, hay una, y solo una, línea en la tabla de tag .

Y, para cada artículo, puede tener varias etiquetas (es decir, varias líneas en la tabla tags_articles ); y, por supuesto, para cada etiqueta, puede tener varios artículos.

Obtener una lista de etiquetas para un artículo, con esta idea, es una cuestión de consulta adicional, como:

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

Obtener los tres artículos "más similares" significaría:

• seleccionar artículos que tienen etiquetas que tiene el primer artículo
• solo use aquellos que tienen el número más importante de etiquetas idénticas

No probado, pero una idea podría ser algo que se vería así:

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

Básicamente, tú:

• seleccione los ID de artículos para cada etiqueta que está presente en su artículo inicial
  • como hay una unión interna, si un artículo en el DB tiene 2 etiquetas que coinciden con la cláusula where , sin la cláusula group by , habría dos líneas para ese artículo
  • por supuesto, no desea volver a seleccionar el artículo que ya tenía, lo que significa que debe excluirse.
• pero, al usar group by article.id , solo habrá una línea por artículo
  • pero podrá usar count , para averiguar cuántas etiquetas tiene cada artículo en común con la inicial
• entonces, solo es cuestión de clasificar por número de etiquetas y obtener solo las tres tres líneas.

Answer 2

En primer lugar, querrá usar la sugerencia de Pascal MARTIN sobre el diseño de la mesa.

En cuanto a encontrar artículos similares, aquí hay algo para que comiences. Dado que @article_id es el artículo para el que desea encontrar coincidencias, y @ tag1, @ tag2, @ tag3 son las etiquetas de ese artículo:

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

Answer 3

sí, pero no respondió mi pregunta principal, ¿cómo obtener 3 artículos más similares?

Respuesta: Solo busque los mismos identificadores de etiquetas en la tabla combinada (tags_articles). Reúnelos y crea un patrón.

Por ejemplo: el artículo 1 tiene etiquetas: 1,2 El artículo 2 tiene etiquetas: 2,3,4 El artículo 5 tiene etiquetas: 6,7,2 El artículo 7 tiene etiquetas: 7,1,2,3

Si desea los 3 artículos más similares para el artículo 1, debe buscar las etiquetas 1,2. Encontrará que el Artículo 7 es el más similar y 2 y 5 tienen algunas similitudes.