[Tagdb] Tagging multiple classes of things

[Erik Hatcher]

On Mar 8, 2006, at 10:21 PM, Colin Viebrock wrote:
> I'm not familiar with anything other than RDBMS ... what would you  
> suggest?

Knowing Otis' (and my) biases, he's implying that an inverted index  
(aka Lucene) is perhaps the best tool for dealing with tags.

I'm doing a something similar - a tagging system for 19th century  
literature objects.  I'm using Lucene as well as Kowari, no RDBMS in  
the picture.

	Erik




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