[Tagdb] Tagging multiple classes of things

[Nitin Borwankar]

Colin,

You could have a table with columns

tagid
articleid
sponsorid
productid

with tagid as primarykey

Then you could do a select on this table, get the respective id's from 
the columns
Then go do selects on the other tables. no "meta" issues since the 
column names tell you what the id applies to.
much faster than doing joins. Also note that all columns except tagid 
could be null in this table.
Hope this is useful

Nitin Borwankar

Colin Viebrock wrote:

> 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