[Tagdb] tags in separate table

[Michal Migurski]

>> It's more efficient and more extensible to search for entities-by- 
>> tag when
>> tags have their own table. The relationships are fully  
>> bidirectional that
>> way.
>>
>> The only way to do this if the tags are stored as a field in an  
>> entity is to
>> use a pattern match or a full text search, both of which are  
>> expensive and
>> hard to manage. I've heard that early versions of Del.icio.us used  
>> the MySQL
>
> I think you misunderstood my question.  I did not mean multiple tags
> in one field, where you need full text search, this would be not a
> relational design.  What I meant was a indexed column for one tag.

Oh, hah, okay. Sorry.

> The only question  in efficiency is what is the difference  in
> efficiency between an index on integer column and index on a char
> column.  I assume integers would be faster but is that a practical
> difference?

Well... you need that char index no matter what. It's just a question  
of whether it's on the tag text in the dedicated tags table or the  
linking table. In the former case you would *also* want an index on  
the numeric ID's in the linking table, but there may be situations  
where you are looking for a tag by ID and not by name, skipping the  
more expensive lookup on the tags-only table. An index on a numeric  
column will generally be smaller than one on a useful char column, so  
there's definitely an index size difference there.

Personally, I'd just use the smaller design with three tables: Users,  
Links, and UserLinkTags with actual tag text stored. Basically, the  
"scuttle" approach from http://www.pui.ch/phred/archives/2005/04/tags- 
database-schemas.html.

A question for Nitin:
Do you actually implement schemas similar to http://tagschema.com/ 
blogs/tagschema/trinity-combined.jpg, or was this just a thought  
exercise? Because it seems to me that such a structure could be  
implemented virtually, using tables as in http://tagschema.com/blogs/ 
tagschema/trinity-triple.jpg with a gob of indexes on the UserTagItem  
table.

------------------------------------------------------
michal migurski- contact info, blog, and pgp key:
sf/ca            http://mike.teczno.com/contact.html

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://nelson.textdrive.com/pipermail/tagdb/attachments/20051012/318191e3/attachment.htm