[Tagdb] Tagging multiple classes of things

[ogjunk-tagdb at yahoo.com]

Copy/paste tagdb at lists.tagschema.com into "To". :)
Late reply.  
As you can see from replies so far, there are lots of ways you can go about it.  I'd add this: is RDBMS really the best tool for what you want to do? :)

Otis
-- 
Simpy -- http://www.simpy.com/ -- Tag.  Search.  Share.

----- Original Message ----
From: Colin Viebrock <cviebrock at tucows.com>
To: tagdb at lists.tagschema.com
Sent: Friday, March 3, 2006 5:25:12 PM
Subject: [Tagdb] Tagging multiple classes of things

I'm looking for some technical suggestions on how to accomplish the 
following:

Say I was running a magazine for DIY-ers.  I have several database 
tables (MySQL, but that shouldn't matter), one for each of the 
following:

    - articles
    - sponsors
    - products

Each table has an ID column, which is unique to that table, and other 
relevant columns (e.g. articles.title, sponsors.name, 
products.featurelist, etc.).

I want to be able to tag everything, so I have a tag table:

    - tags
        - tag_id
        - tag

And now I want to build the relationships between tags-articles, 
tags-advertisers and tags-products.  I could do it with 3 different 
tables:

    - tag-articles
        - tag_id
        - article_id

    - tag-sponsors
        - tag_id
        - sponsor_id

    - tag-products
        - tag_id
        - product_id

But that seems problematic when I want to do a search for everything 
tagged with "foobar".  I'll need to do three queries:

    SELECT ... FROM tags LEFT JOIN tag-articles USING (tag_id) LEFT JOIN 
articles USING (article_id)
    SELECT ... FROM tags LEFT JOIN tag-sponsors USING (tag_id) LEFT JOIN 
sponsors USING (sponsors_id)
    SELECT ... FROM tags LEFT JOIN tag-products USING (tag_id) LEFT JOIN 
products USING (products_id)

So, what if I built a generic relationship table:

    - tag-relations
        - tag_id
        - foreign_table
        - foreign_id

This table would have entries like:

    1, 'articles', 3
    1, 'sponsors', 5
    1, 'products', 28
    1, 'products', 36
    ...

i.e. the tag with tag_id=1 applies to article #3, sponsor #5 and 
products #28 and #36.

The thing is, you can't do the kind of "meta" select required, not in 
MySQL at least AFAIK, so you are back to making multiple queries.

Any ideas on how best to implement this kind of system: where one 
database of tags needs to be shared with several other databases?  
Maybe the multiple query solution is the only way ... I'd just like to 
know that before I start architecting.  :)

- Colin

_______________________________________________
Tagdb mailing list
Tagdb at lists.tagschema.com
http://lists.tagschema.com/mailman/listinfo/tagdb