最佳数据库(MySQL)结构:包含优先标签的文章 [英] Best DB (MySQL) structure: Articles which contain favored tags

问题描述

我建立了一个新闻网站: -文章按日期显示在首页上.最新的第一. -新闻位于新闻"表中，其中包含"id"，"title"，"text"和其他一些字段. -所有文章均带有1-5个相关标签. -标签在表格标签"中，字段为"id"，标签"，文章"和其他一些字段. -标签"的文章"字段适合新闻"的"id"字段.

I've built a news site: - The articles are shown on the front page ordered by date. The newest one first. - The news are in the table "news" with the fields "id", "title", "text" and some other ones. - All articles are tagged with 1-5 relevant tags. - The tags are in the table "tags" with the fields "id", "tag", "article" and some other ones. - The field "article" of "tags" fits to the field "id" of "news".

现在，我想让用户有机会将标签添加到他的收藏的标签列表"中.然后，用户应只看到包含偏爱标签之一的新闻报道.

Now I want to give the user the opportunity to add tags to his "favored tags list". Then the user should only see news articles which contain one of the favored tags.

假设用户Bob偏爱标签"barack obama"，"nba"，"new jersey"和"dogs".他应该只看到包含至少四个标签之一的文章.

Assuming the user Bob has favored the tags "barack obama", "nba", "new jersey" and "dogs". He should only see articles containing at least one of these four tags.

如何编写实现此目的的PHP/MySQL脚本?我认为我的数据库结构不足以实现此目的，是吗?我必须像这样进行数据库查询:

How could I code a PHP/MySQL script which achieves this? I think my database structure is not adequate for this purpose, is it? I would have to make DB queries like this:

选择*从新闻中输入ID(从标记中选择文章，在标记中(巴拉克·奥巴马"，"nba"，新泽西"，狗"))"

"SELECT * FROM news WHERE id IN (SELECT article FROM tags WHERE tag IN ('barack obama', 'nba', 'new jersey', 'dogs'))"

此查询将运行很长时间，不是吗?必须有一个比我的数据库更合适的数据库结构.您对这个问题有想法吗?我需要哪种数据库结构，然后必须使用什么查询?

This query would run for a long time, wouldn't it? There must be a database structure which is more appropriate than mine. Do you have an idea for this problem? Which DB structure do I need and what queries must I use then?

希望您能帮助我.预先感谢！

I hope you can help me. Thanks in advance!

推荐答案

以下内容并非详尽无遗，但应该可以使您朝正确的方向前进.

The following is by no means exhaustive/definitive, but it should get you going in the right direction.

news
=====
id
title
text

tag
===
id
tag

tag_map
=======
tag_id
news_id

favorite_tags
=============
user_id
tag_id

查询

SELECT * 
FROM favorite_tags
JOIN tag_map ON favorite_tags.tag_id = tag_map.tag_id
JOIN news ON tag_map.news_id = news.id
WHERE favorite_tags.user_id = $userid

这篇关于最佳数据库(MySQL)结构:包含优先标签的文章的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！