One-to-Many Relationship in (Postgre) SQL

Question

I have two tables:

posts:

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

and tags:

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

posts.tags and tags.tag are both types of text. What I want is the relation from tags.tag to the lines in the messages, so the <foo> request will give me the lines corresponding to messages 1 and 2, the <blah> request gives me 2 and 3, <bar> gives me 1 and 3, etc.

I looked at foreign keys, but I'm not sure what I want. (and to be honest, I'm not quite sure what he is doing). From what I can tell, the foreign key should be equal to the primary key / unique column of the table. But I want all the lines such that posts.tags ~ '.*<foo>.*' Etc. I also want to be able to, say, get all the tags starting with b, for example:

 CREATE VIEW startswithB AS SELECT tag FROM tags WHERE tag ~ '<b.*>'; SELECT DISTINCT * FROM posts, startswithB WHERE posts.tags ~ ('.*' || startswithB || '.*'); 

How do I get the attitude I'm looking for? Is it possible?

EDIT:

Good thing I did:

Create post_tags:

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

select all posts tagged with <foo> :

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

Answer 1

What you are actually continuing here is a many-to-many relationship. Think about it: each tag can be on multiple posts, and each post can have multiple tags.

The correct relational architecture for this is to add another table in the middle:

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

Then release the tags column in the message table.

This solves all your problems because you can get a set of tags for a post or a set of messages with a given tag by joining post_tags in different directions. You can also get a list of tags that start with something with a regular LIKE query, which will be more difficult if you have a group of strings combined in one field.

Answer 2

As Daniel mentioned, you have a many-to-many relationship. Just to clarify how all 3 tables will look with the many-to-many setting:

Posts:

  id | ... other stuff ... ---+--------------------- 1 | ... 2 | ... 

Tags:

  tag --- <foo> <bar> 

Post_Tags mapping table:

  post_id | tag --------+------ 1 | <foo> 1 | <bar> 

Answer 3

Normalize the data model. Here is one way to represent the M: N relationship you have:

Please note that POST_TAG PK {POST_ID, TAG}, not just {POST_ID}.

A search for all posts tagged with 'foo' will look like this:

 SELECT * FROM POST WHERE POST_ID IN ( SELECT POST_ID FROM POST_TAG WHERE TAG = 'foo' ) 

For posts tagged with a tag that begins with 'f', you can do this:

 SELECT * FROM POST WHERE POST_ID IN ( SELECT POST_ID FROM POST_TAG WHERE TAG LIKE 'f%' )