[eluser]Xeoncross[/eluser]
SO tagging has become the world's answer to the problem of finding stuff - from Firefox to Wordpress - tags are everywhere!
I want to setup a database that will provide the best way to handle tags. So I have the following three tables:
Code:
CREATE TABLE `posts` (
`id` int(11) NOT NULL auto_increment,
`title` varchar(255) NOT NULL,
`text` text NOT NULL,
PRIMARY KEY (`id`)
);
INSERT INTO `posts` (`id`, `title`, `text`) VALUES
(1, 'My Post 1', 'Text of Post'),
(2, 'My Post 2', 'Text of Post 2');
CREATE TABLE `tags` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(100) NOT NULL,
PRIMARY KEY (`id`)
);
INSERT INTO `tags` (`id`, `name`) VALUES
(1, 'used_tag'),
(2, 'not_used_tag'),
(3, 'used_tag_2'),
(4, 'not_used_tag_2'),
(5, 'used_tag_3'),
(6, 'not_used_tag_3');
CREATE TABLE `tags_ref` (
`tags_id` int(11) NOT NULL,
`item_id` int(11) NOT NULL,
`table` varchar(100) NOT NULL
);
INSERT INTO `tags_ref` (`tags_id`, `item_id`, `table`) VALUES
(1, 1, 'posts'),
(3, 2, 'posts'),
(5, 1, 'posts'),
(5, 2, 'posts');
The way I imagine this working is:
1) The "tags" table holds every tag for any table that will ever use tags. That way I don't have two different tables holding the tag "sports".
2) The "tags_ref" table holds the ID of another tables row (item_id), the id of a matching tag (tag_id), and the name of the table that the item_id belongs to (table).
3) The "posts" table is just one of many tables that will make use of tags. It is just here for this example.
Now, I thought that I could get something like this going:
Code:
SELECT `posts`.`id` , `posts`.`title` , `posts`.`text` , `posts`.`date` , `posts`.`author` , `tags`.`name`
FROM `posts`
LEFT JOIN `tags_ref` ON `posts`.`id` = `tags_ref`.`item_id`
LEFT JOIN `tags` ON `tags_ref`.`tags_id` = `tags`.`id`
WHERE `posts`.`id` = 1
However, the above query results in two rows:
Code:
id title text name
1 My Post 1 This is a post that will have a couple tags attach... used_tag
1 My Post 1 This is a post that will have a couple tags attach... used_tag_3
So I guess I just need to make two queries - one to get the post - an another to get the tags that match that post.
Code:
//Get the post
SELECT * FROM `posts` WHERE `id` = '3'
//Then get the tags
SELECT * FROM `tags_ref`
LEFT JOIN `tags` ON `tags_ref`.`tags_id` = `tags`.`id`
WHERE `tags_ref`.`table` = 'posts' AND `tags_ref`.`item_id` = '3'
So anyway, I am just trying to work through this and I want to know if anyone has any ideas about the best way to handle tags when posts, comments, links, and many other tables might make use of tags.
