MySQL & PHP Tagging

Question

Hi, I'm creating a webapp. I want to support tags. How have to be the MySql table?. I want to relate a link, with various tags. Thanks.

Answer 1

You could do something like:

CREATE TABLE tags (
linkid int(10),
tag varchar(20),
primary key(linkid,tag)
);

So you would have tuples of (linkid,tag)

Answer 2

The following format allows multiple tags per link and allows links to share the same tags. This allows you to easily see which links are popular, and at the same time avoid storing duplicate tags.

Schema

Links
  LinkID
  Url
  Name

Tags
  TagID
  Name

LinkTags
  LinkID
  TagID

Sample data:

Links
---------------------------------------------------
LinkID      URL                    Name
1           http://avira.com/      Avira Antivirus
2           http://microsoft.com/  Microsoft
---------------------------------------------------

Tags
---------------------------------------------------
TagID       Name
---------------------------------------------------
1           Software
2           Antivirus
3           Operating Systems
---------------------------------------------------

LinkTags
---------------------------------------------------
LinkID      TagID
---------------------------------------------------
1           1
1           2
2           1
2           3

Queries

Links and tags:

SELECT Links.*, GROUP_CONCAT(Tags.Name) AS TagList
FROM Links
LEFT JOIN LinkTags
  ON LinkTags.LinkID = Links.LinkID
LEFT JOIN Tags
  ON Tags.TagID = LinkTags.TagID
GROUP BY Links.LinkID

Ten most popular tags:

SELECT Tags.*
FROM LinkTags
LEFT JOIN Tags
  ON Tags.TagID = LinkTags.TagID
GROUP BY LinkTags.TagID
ORDER BY (COUNT(LinkTags.LinkID)) DESC
LIMIT 10

Answer 3

CREATE TABLE links (
    id  int(10)
    ...

    ,primary key(id)
);

CREATE TABLE tags (
    id  int(10)
   ,tag varchar(20)

   ,primary key(id)
);

CREATE TABLE tagged_links (
    id   int(10)
   ,tag  int(10)
   ,link int(10)

   ,primary key(id)
   ,foreign key(tag) references tags(id)
   ,foreign key(link) references links(id)
);