On 25 Jul 2009, at 3:20am, P Kishor wrote: > I am trying to develop a "tagging" system, whereby each row in a table > can be tagged with arbitrary number of tags.
As an alternative for using a table for tags, consider using a long string instead. The default value for this column would be the character (picking one at random) ','. To add a tag you append the tag plus another ',' to it. So a record tagged as both 'hot' and 'current bug' would have this value in the 'tags' column: ,hot,current bug, To search for all rows containing a specific tag, use the 'LIKE' operator to find all strings including ','||thisTag||','. To show all the tags apart from that one, use a 'replace' expression to remove it from the string or use whatever your programming languages uses for 'replace'. To forget all uses of the tag use an UPDATE command with a 'replace' expression. This solution does not suit every application, since it's slower for searching big tables, but this way of implementing tags allows operations like the one you describe to be done as a single simple SELECT statement with no JOIN or SELECT arguments. It does not require you to carefully modify a 'tags' table when you delete a record. It removes the requirement for one table and one index, and thus makes the overall database smaller. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
