CodeIgniter Forums
Managing TAGS for many different tables - Design Logic Help - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forum-20.html)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forum-23.html)
+--- Thread: Managing TAGS for many different tables - Design Logic Help (/thread-10915.html)



Managing TAGS for many different tables - Design Logic Help - El Forum - 08-18-2008

[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.


Managing TAGS for many different tables - Design Logic Help - El Forum - 08-19-2008

[eluser]gon[/eluser]
Perhaps you need one more table:

A table containing just 1 column: a site-wide ID. Let's call it RESOURCE_ID.

Every table using tags would have its own unique key ID, AND also a column called RESOURCE_ID.

For example, if you have posts and comments, post number 1 could have RESOURCE_ID = 1. Comment number 1 would have RESOURCE_ID = 2.

The trick is assigning the tags to the RESOURCE_ID, not to the unique key ID of the tables.

So there aren't collisions between 2 rows of different tables that happen to have the same ID. (the post 1 and comment 1 for example).


Managing TAGS for many different tables - Design Logic Help - El Forum - 08-19-2008

[eluser]codex[/eluser]
Take a look at 'freetag'. It's an easy to implement ready to use tag class. You may not wanna use the class itself, but it gives you a pretty good idea on how to solve things.

It's somewhere in the Wiki also.


Managing TAGS for many different tables - Design Logic Help - El Forum - 08-19-2008

[eluser]Xeoncross[/eluser]
[quote author="gon" date="1219154892"]A table containing just 1 column: a site-wide ID. Let's call it RESOURCE_ID.[/quote]

So that would mean that you would do the following:

Code:
//Get post
SELECT * FROM `posts` where `id` = 1
//GET $row so we know what resource ID to get

//Get the resouce_ID so we can find the matching tags
SELECT * FROM `RESOURCE` WHERE `RESOURCE_ID` = '. $row['RESOURCE_ID'];
//Why bother though?

SELECT * FROM `tags` WHERE `RESOURCE_ID` = '. $row['RESOURCE_ID'];

I don't see any point at all in having a table with only one column since you would have to already know the resouce_id just to find it in the table. Wink

Plus, in the method I said there would never be two matching columns (like comments and posts) because you would use the "table" column to only fetch tags that matched the POST_ID and the table POSTS.


Freetag does kind of what I suggested.
Code:
CREATE TABLE freetags (
  id int(10) unsigned NOT NULL auto_increment,
  tag varchar(30) NOT NULL default '',
  raw_tag varchar(50) NOT NULL default '',
  PRIMARY KEY  (id)
) TYPE=MyISAM;

CREATE TABLE freetagged_objects (
  tag_id int(10) unsigned NOT NULL default '0',
  tagger_id int(10) unsigned NOT NULL default '0',
  object_id int(10) unsigned NOT NULL default '0',
  tagged_on datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`tag_id`,`tagger_id`,`object_id`),
  KEY `tag_id_index` (`tag_id`),
  KEY `tagger_id_index` (`tagger_id`),
  KEY `object_id_index` (`object_id`)
) TYPE=MyISAM;



Managing TAGS for many different tables - Design Logic Help - El Forum - 08-19-2008

[eluser]codex[/eluser]
[quote author="Xeoncross" date="1219184597"]
Freetag does kind of what I suggested.
Code:
CREATE TABLE freetags (
  id int(10) unsigned NOT NULL auto_increment,
  tag varchar(30) NOT NULL default '',
  raw_tag varchar(50) NOT NULL default '',
  PRIMARY KEY  (id)
) TYPE=MyISAM;

CREATE TABLE freetagged_objects (
  tag_id int(10) unsigned NOT NULL default '0',
  tagger_id int(10) unsigned NOT NULL default '0',
  object_id int(10) unsigned NOT NULL default '0',
  tagged_on datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`tag_id`,`tagger_id`,`object_id`),
  KEY `tag_id_index` (`tag_id`),
  KEY `tagger_id_index` (`tagger_id`),
  KEY `object_id_index` (`object_id`)
) TYPE=MyISAM;
[/quote]

Freetag does everything regarding tagging. No need to reinvent the wheel. :coolsmirk:


Managing TAGS for many different tables - Design Logic Help - El Forum - 08-19-2008

[eluser]gon[/eluser]
I always use InnoDB tables, and foreign keys.
In that case it makes sense to have a one column table, with auto_increment.
When inserting in any other table, you use the one-column table to get the next RESOURCE_ID, and then make the insert in the other table.

Obviously, the one-column table is not used when doing selects.

Anyway, I read your post too fast, so my suggestion may not be very useful.


Managing TAGS for many different tables - Design Logic Help - El Forum - 08-19-2008

[eluser]Xeoncross[/eluser]
[quote author="codex" date="1219191185"][quote author="Xeoncross" date="1219184597"]
Freetag does everything regarding tagging. No need to reinvent the wheel. :coolsmirk:[/quote]

Nope. It doesn't support multiple tables. Plus even the http://codeigniter.com/wiki/Freetag/ isn't complete.

I want a tag system to end all tag systems that will allow me to tag any type of table with tags. In other words, the tags aren't tied to a specific table.

Quote:I always use InnoDB tables, and foreign keys.
In that case it makes sense to have a one column table, with auto_increment.
When inserting in any other table, you use the one-column table to get the next RESOURCE_ID, and then make the insert in the other table.

There is no need for this though. $this->db->insert_id() covers the need that might arise from inserts.


Thanks for the comments though ;-)


Managing TAGS for many different tables - Design Logic Help - El Forum - 08-19-2008

[eluser]codex[/eluser]
[quote author="Xeoncross" date="1219195907"][quote author="codex" date="1219191185"][quote author="Xeoncross" date="1219184597"]
Freetag does everything regarding tagging. No need to reinvent the wheel. :coolsmirk:[/quote]

Nope. It doesn't support multiple tables. Plus even the http://codeigniter.com/wiki/Freetag/ isn't complete.

I want a tag system to end all tag systems that will allow me to tag any type of table with tags. In other words, the tags aren't tied to a specific table.
[/quote]

That's why I altered the class to my own needs. But as a starting point it was very helpful.

Anyway, good luck and let me/us know how you tackle this tagging stuff. I'm very curious to see how you do.


Managing TAGS for many different tables - Design Logic Help - El Forum - 08-19-2008

[eluser]Xeoncross[/eluser]
Looks like my only choice is a re-write of freetags. Wink
Well, I know what I am going to be doing the next couple days...

function list:
Code:
tags_ref = object_id that matches tags

add
->tags
->tag_ref for object_id

update
->tags
->tags_ref for object_id

delete
->tags
->tags_ref for object_id

fetch
->tag_id from tag
->tag from tag_id

->tags for object_id
->object_id's for tag_id

->tags like tag (based on objects linked)
->fetch objects like object (based on tags linked)

->most popular tags
->newest tags