database - 我应该如何设计表以将标签存储在数据库中？

Question

"question_id": 58640
"tags": ["polls", "fun", "quotes"]
"title": "Great programming quotes"
"question_id": 184618
"tags": ["polls", "fun", "comment"]
"title": "What is the best comment in source code you have ever encountered?"
"question_id": 3734102
"tags": ["c++", "linux", "exit-code"]
"title": "Why cant' I return bigger values from main function ?"
"question_id": 2349378
"tags": ["communication", "terminology", "vocabulary"]
"title": "New programming jargon you coined?"
"question_id": 3723817
"tags": ["open-source", "project-management", "failure", "fail"]
"title": "How to make an open source project fail"
"question_id": 3699150
"tags": ["testing", "interview-questions", "job-interview"]
"title": "Interview question please help"

这只是我使用 SO API 得到的一些问题的文本摘录.

要使此查询可用，我想使用 SQLite存储数据。

How should I store the tags column?

由于 SO 的限制是五个标签，我可以使用五列 tag1、tag2 ...，但我认为可以做一些更优雅的事情。可以扩展到任意数量的标签的东西，也可以处理基本的查询，比如

select title from table where tag has "c++" and "boost" but not "c"

Answer 1

这是一个多对多的关系:问题有多个标签，标签可以出现在多个问题中。这意味着您必须创建三张表，一张用于问题，一张用于标签，一张用于这些表之间的链接。结果查询将如下所示:

SELECT title FROM question
       INNER JOIN question_tag_link USING (question_id)
       INNER JOIN tag USING (tag_id)
            WHERE tag_name IN('c++', 'boost')
              AND NOT EXISTS(
           SELECT * FROM tag t1
            WHERE t1.tag_name = 'c'
              AND t1.question_id = question.question_id);

没那么简单，但我想这是不想被限制所要付出的代价。如果少于 64 个不同的标签，您可以使用 SET 字段类型，但是您会失去很大的灵 active (很难添加新标签)。