（Postgre）SQL中的一对多关系

Question

我有两个表：

帖子：

 id | ... other stuff ... |     tags                         
----+---------------------+--------------
  1 |         ...         | <foo><bar>
  2 |         ...         | <foo><baz><blah>
  3 |         ...         | <bar><blah><goo>

和标签：

     tag         
--------------
 <foo>
 <bar>
 <baz>
 <blah>
 <goo>

posts.tags和tags.tag都是文本类型。我想要的是从tag.tag到帖子中的行的关系，这样查询<foo>将给我对应于帖子1和2的行，查询<blah>给我2和3，<bar>给我1和3，依此类推。

我已经看过外键，但是我不确定这是我想要的。（老实说，我不确定它是做什么的）。据我所知，外键必须等于表的主键/唯一列。但是我想要的是诸如此类的所有行posts.tags ~ '.*<foo>.*'。我还希望能够获得以b开头的所有标签，例如：

CREATE VIEW startswithB AS
SELECT tag
FROM tags
WHERE tag ~ '<b.*>';

SELECT DISTINCT * FROM posts, startswithB WHERE posts.tags ~ ('.*' || startswithB || '.*');

如何获得我要寻找的关系？是否有可能？

编辑：

好的，我已经完成了：

创建post_tags：

SELECT posts.id, tags.tag 
INTO post_tags 
FROM posts, tags 
WHERE posts.tags ~ ('.*' || tags.tag || '.*');

选择所有带有标签的帖子<foo>：

SELECT *
FROM posts
WHERE posts.id IN (
    SELECT id
    FROM post_tags
    WHERE tag = '<foo>'
);

Answer 1

您实际上在这里发生的是多对多关系。考虑一下：每个标签可以位于多个帖子上，并且每个帖子可以具有多个标签。

正确的关系体系结构是在中间添加另一个表，如下所示：

CREATE TABLE post_tags (
  id INTEGER REFERENCES posts,
  tag VARCHAR REFERENCES tags
);

然后将该tags列放在您的帖子表上。

这可以解决所有问题，因为您可以通过沿不同方向加入post_tags来获得帖子上的标签集或具有给定标签的帖子集。您还可以使用常规的LIKE查询来获取以某些内容开头的标签列表，如果您在一个字段中串接了一串字符串，这将更加困难。