• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Managing TAGS for many different tables - Design Logic Help

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

#2
[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).

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

#4
[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;

#5
[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:

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

#7
[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 ;-)

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

#9
[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


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.