With only a little of previous knowledge about databases with no formal education together, I am a bit stuck regarding how you can model this (and retrieve the information I require from this in PHP). This is exactly what I am attempting to model:

For every item on my small site, it's permitted to possess multiple tags for example file, upload, php, recursive etc. Nevertheless the tags are multiple-use, I'm able to have two different products plus they each might have the php tag.

I have been attempting to educate yourself regarding how to do that and whether it's lack of skill by using it or something like that else I'm not sure, however i can't appear to understand the idea. Apparently you'll need a middle table which links the 2 together?

Also after i have this relationship and also the tables defined, wouldso would I actually do items like: - Retrieve all products having a certain tag? - Retrieve all tags that certain item has?

Interesting help, and if anybody could list any more reading through about this to bolster my knowledge of the notion that could be great.

The db part is simple. Case an example so that you can observe how db look like, no 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 obtain products with yourTag tag you'll just run query such as 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 obtain tags connected with publish with id of 123 you take this question:

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 that PHP part you could utilize a framework. Many (if not completely) frameworks can certainly model such associations. For instance in CakePHP this done like this:

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'
        )
    );
}

You need to use medium difficulty table, to relate the 2 organizations:


--------     1:n    ------------          ---------

 ITEM -¦---------< ITEM_TAG    n:1      TAG  

 Id                ItemId   >-------¦- Id    

 Title              TagId               Title  

¯¯¯¯¯¯¯¯            ¯¯¯¯¯¯¯¯¯¯¯¯          ¯¯¯¯¯¯¯¯¯

Then for querying the information, you need to join your tables inside a choose statement:

All of the products within 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 of the tags for that item with title "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'

You are right, many-to-many associations are implemented using additional table, for example:

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

Any procedures are now being done using multiple joins. For example, if you wish to choose all records with tag 'foo', using tables from the example, you need 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 particular 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