CODEWITHSUNDEEP
mysqldatabasesqliterelationship
ilmiont
ilmiont

Reputation: 2161

Creating an N:M relationship in SQLite

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

Upvotes: 0

Views: 2369

Answers (2)

neilh
neilh

Reputation: 706

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.

Upvotes: 1

geoandri
geoandri

Reputation: 2428

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

Upvotes: 1

Related Questions