[Tagdb] Tagging multiple classes of things

[Colin Viebrock]

I'm not familiar with anything other than RDBMS ... what would you 
suggest?

- Colin


On 8-Mar-06, at 4:21 PM, <ogjunk-tagdb at yahoo.com> wrote:

> 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
>
>
>
> _______________________________________________
> Tagdb mailing list
> Tagdb at lists.tagschema.com
> http://lists.tagschema.com/mailman/listinfo/tagdb