0

I am getting confused with making a N:M relationship between two tables in SQLite.

My tables are "posts" and "tags", both components of my CMS I am making for my blog.

I want to link the tags column of the posts table to the tags table so that I can link multiple tags (defined in the tags table with an id, name and description) to the post using just one row of the database in the tags column of the posts table.

I have tried various things since being told to use an N:M relationship, something I hadn't used before being new to SQL. I understand what needs to be done - point the "tags" column to the tags table - but am not sure how to execute this.

I have tried foreign keys, indexes and more according to these links but am not sure what I should be doing... although I acknowledge that the answer is almost certainly right beneath my nose on these pages.

http://www.singingeels.com/Articles/Understanding_SQL_Many_to_Many_Relationships.aspx

SQLite many-to-many relationship?

Is it simply going to be FOREIGN KEY(tags) REFERENCES tags(id) in the posts table? Although I don't see how this enables multiple entries for "tags" in the posts table.

Any advice appreciated and I am continuing to try and educate myself right now,

Ilmiont

Community
  • 1
  • 1
Ilmiont
  • 2,032
  • 4
  • 27
  • 44

2 Answers2

1

In the case of Many to Many relationships you have to create an extra table except from posts and tags( for example posts_tags) in which you store the relationship between posts and tags. This table should have columns post_id (foreign key references to posts.id) and tag_id (foreign key references to tags.id). For example if post with id=1 has tags with ids 1,2,3 you will have 3 rows like

 post_id | tag_id
     1   |   1
     1   |   2
     1   |   3

For the reverse relationship . If you have articles with ids 3,4,5 tagged with the tag with id = 5 then your table will be 

 post_id | tag_id
     3   |   5
     4   |   5
     5   |   5

Hope it helps

geoandri
  • 2,360
  • 2
  • 15
  • 28
  • Thanks, this has helped me understand it better. I will give it a try. – Ilmiont Oct 24 '14 at 13:02
  • Hmm I have another question I'm afraid.. how do I go about inserting data into the relationship? The most obvious thing to me is to insert the post details , and then use LAST_INSERT_ID() to add the data to the tags / post_tags tables but would this actually work? – Ilmiont Oct 24 '14 at 13:26
  • A create post function will require inserting data to both tables. One to the post table and one to the posts_tags table for relating new post with tags. – geoandri Oct 24 '14 at 13:50
1

This is commonly called a Many-to-Many Relationship and you may have better luck searching for more examples with that phrase.

More immediately though you need a third table with foreign keys to both posts and tags, as in the other SO question you linked. Note the foobar table there at the end. In your case you need a post_tags table. That table can then have multiple rows for a post (N), and a single post in that table can have multiple rows with different tags (M), getting you your N:M relation.

neilh
  • 706
  • 3
  • 9