How to model the relationship of many, many?

Question

Having only a little previous experience working with databases and without formal education with them, I am a little fixated on how to model this (and get the data that I need from it in PHP). This is what I am trying to simulate:

For each element on my site, it is allowed to have several tags, such as file, download, php, recursive, etc. However, tags are reused, I can have two different elements, each of which can have a php tag.

I tried to find out how to do this, and is he experiencing experience or something else that I don’t know, but I can’t understand the concept. Apparently you need a middle table that ties them together?

Also, as soon as I have this relationship and certain tables, how will I do things like: - Get all the elements with a specific tag? - Get all the tags that one element has?

Thank you for your help, also, if someone could list further readings on this subject to strengthen my understanding of the concept, which would be great.

Answer 1

The db part is simple. This is just a sample so you can see how db might look, not some SQL query 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 retrieve items with a tag yourTag, you simply run a 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';

, 123, :

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;

PHP . ( ) . , CakePHP :

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

:

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

join select:

"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'

"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'

Answer 3

, " " , :

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

. , 'foo', , :

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

() , ( 123):

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 4

, " " , , .

Part of the database is not so difficult, it is more difficult to use in the code with all these left joins, and this can become quite dirty :)

My advice is to use an ORM structure such as Doctrine or Propel (although I prefer Doctrine) that even handle some complex queries for you.