Managing TAGS for many different tables - Design Logic Help |
[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` ( 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` However, the above query results in two rows: Code: id title text name 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 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.
[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).
[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.
[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 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. 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 (
[eluser]codex[/eluser]
[quote author="Xeoncross" date="1219184597"] Freetag does kind of what I suggested. Code: CREATE TABLE freetags ( Freetag does everything regarding tagging. No need to reinvent the wheel. :coolsmirk:
[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.
[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. There is no need for this though. $this->db->insert_id() covers the need that might arise from inserts. Thanks for the comments though ;-)
[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.
[eluser]Xeoncross[/eluser]
Looks like my only choice is a re-write of freetags. Well, I know what I am going to be doing the next couple days... function list: Code: tags_ref = object_id that matches tags |
Welcome Guest, Not a member yet? Register Sign In |