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

问题描述

我建立了一个新闻网站:- 文章按日期排序显示在首页.先上最新的.- 新闻在新闻"表中，包含id"、title"、text"和其他一些字段.- 所有文章都标有 1-5 个相关标签.- 标签位于标签"表中，其中包含id"、标签"、文章"和其他一些字段.- tags"的article"字段适合news"的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:

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

"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屋！