How to model a many to many relationship?

Question

With only a bit of previous experience with databases and no formal education with them, I'm a bit stuck as to how to model this (and retrieve the data I require from it in PHP). This is what I'm trying to model:

For each item on my site, it is allowed to have multiple tags such as file, upload, php, recursive etc. However the tags are reusable, I can have two different items and they each could have the php tag.

I've been trying to read up on how to do this and whether it is lack of experience with it or something else I don't know, but I can't seem to grasp the concept. Apparently you need a middle table which links the two together?

Also once I have this relationship and the tables defined, how would I do things such as: - Retrieve all items with a certain tag? - Retrieve all tags that one item has?

Thanks for your help, also if anyone could list any further reading on this to strengthen my understanding of the concept that would be great.

Answer 1

The db part is easy. This is just a sample so you can see how db can look like, not any particular SQL engine queries.

CREATE TABLE posts (
    id INT PRIMARY KEY,
    subject VARCHAR(100),
    body TEXT
)

CREATE TABLE tags (
    id INT PRIMARY KEY,
    name VARCHAR(50)
)

CREATE TABLE post_tags (
    post_id INT,
    tag_id INT,
    FOREIGN KEY (post_id) REFERENCES posts (id),
    FOREIGN KEY (tag_id) REFERENCES posts (id)
)

To get items with yourTag tag you will just run query like this

SELECT P.*
FROM posts P 
    LEFT JOIN post_tags PT ON (PT.post_id = P.id)
    LEFT JOIN tags T ON (T.id = PT.tag_id)
WHERE T.name = 'yourTag';

To get tags associated with post with id of 123 you run this query:

SELECT T.*
FROM tags T 
    LEFT JOIN post_tags PT ON (T.id = PT.tag_id)
    LEFT JOIN posts P ON (PT.post_id = P.id)
WHERE P.id = 123;

For the PHP part you could use a framework. Many (if not all) frameworks can easily model such relationships. For example in CakePHP this done like that:

class Post extends AppModel {
    $useTable = 'posts';
    $hasAndBelongsToMany = array(
        'Tag' => array(
            'className' => 'Tag'
            'joinTable' => 'post_tags'
            'foreignKey' => 'post_id'
            'associationForeignKey' => 'tag_id'
        )
    );
}

class Tag extends AppModel {
    $useTable = 'tags';
    $hasAndBelongsToMany = array(
        'Post' => array(
            'className' => 'Post'
            'joinTable' => 'post_tags'
            'foreignKey' => 'tag_id'
            'associationForeignKey' => 'post_id'
        )
    );
}

Answer 2

You're right, many-to-many relationships are implemented using additional table, for instance:

Blog_entry(entry_id, entry_body)
Tag(tag_id, tag_name)
Entry_tag(entry_id, tag_id)

Any operations are being done using multiple joins. For instance, if you want to select all entries with tag 'foo', using tables from my example, you have to execute:

select * 
from
    blog_entry, tag, entry_tag
where
    tag.tag_name = 'foo' and
    entry_tag.tag_id = tag.tag_id and
    entry_tag.entry_id = blog_entry.entry_id

(update) To retrieve all tags that certain entry (here with ID 123) has:

select tag_name
from
    blog_entry, tag, entry_tag
where
    Blog_entry.entry_id = 123
    entry_tag.tag_id = tag.tag_id and
    entry_tag.entry_id = blog_entry.entry_id

Answer 3

Yeah, many-to-many relationship needs additional, third table, called association table.

Database part is not that hard, it's harder to use it in code with all those left joins and it can get pretty messy :)

My advice is to use ORM framework, like Doctrine or Propel (though I prefer Doctrine), which handle even some complex queries for you.

Answer 4

You should use an intermediate table, to relate the two entities:

--------     1:n    ------------          --------- 
| ITEM |-¦---------<| ITEM_TAG |   n:1    |  TAG  |
| Id   |            | ItemId   |>-------¦-| Id    |
| Name |            | TagId    |          | Name  |
¯¯¯¯¯¯¯¯            ¯¯¯¯¯¯¯¯¯¯¯¯          ¯¯¯¯¯¯¯¯¯

Then for querying the data, you should join your tables in a select statement:

All the items in the tag "FooTag"

SELECT item.* FROM item 
              JOIN item_tag on item.id = item_tag.itemId
              JOIN tag on item_tag.tagId = tag.id
WHERE tag.Name = 'FooTag'

All the tags for the item with name "FooItem"

SELECT tag.* FROM tag 
             JOIN item_tag on tag.id = item_tag.tagId
             JOIN item on item_tag.itemId = item.id
WHERE item.Name = 'FooItem'