You can have the tags in a separate table that has a foreign-key to the table with the rows in in you want to tag, and then do a select from that table to find the tagged rows. If you need to search for multiple tags at a time, each requires a join: select t from tagged as t join t.tags as tag1 join t.tags as tag2 where tag1 = some value and tag2 = some value.
----- Original Message ----- From: "Jay A. Kreibich" <j...@kreibi.ch> To: <punk...@eidesis.org>; "General Discussion of SQLite Database" <sqlite-users@sqlite.org> Sent: Friday, July 24, 2009 9:21 PM Subject: Re: [sqlite] a system for arbitrarily tagging rows in a table > On Fri, Jul 24, 2009 at 09:20:29PM -0500, P Kishor scratched on the > wall: >> I am trying to develop a "tagging" system, whereby each row in a >> table >> can be tagged with arbitrary number of tags. > > This smells of a Relational division problem. If you're dealing > with > tags you might want to have a look at that (Celko has a few good > articles on it). Since SQL lacks a native Relational division > operator, chances are a solution in that direction is going to be > more complex -- at least for this problem. But any time I've done > tags or attributes, sooner or later I find myself needing to do a > division. They come in handy any time you say "my data is vertical > but I need it horizontal." You might want to read up on them just > to > have that knowledge available. > > >> TABLE foo (f_id INTEGER PRIMARY KEY, f_name TEXT); >> TABLE tag (t_id INTEGER PRIMARY KEY, t_name TEXT); >> TABLE foo_tag (f_id INTEGER, t_id INTEGER); > > >> I have the following solution. Could I do better or differently? > > I'm not sure about "better", but here's different: > > sqlite> SELECT foo.f_name, tag.t_name > ...> FROM tag AS target > ...> NATURAL JOIN foo_tag AS target_ft > ...> NATURAL JOIN foo > ...> NATURAL JOIN foo_tag > ...> NATURAL JOIN tag > ...> WHERE target.t_name = 'bar' > ...> AND tag.t_id != target.t_id > ...> ORDER BY foo.f_name, tag.t_name; > > This basically folds your IN sub-select back into the main query. > We join "foo" to the tag table in two directions... one to find > the search target tag id and the other to produce the output. > > "tag AS target" with the first WHERE clause should return one row. > We join that through "foo_tag AS target_ft" to get a list of foo > ids > that have the search target tag. We then build the normal output > list by joining that back through the foo_tag bridge table to the > tags, and throw out any rows with an output tag id that matches the > search target tag id. > > Simple! > > Best of all, the target only appears in the query once, and your > name > convention means we can use NATURAL JOINs to keep things clean. It > also totally falls apart if you need to search on more than one > tag. > That's where Relational division comes in. > > -j (who had to draw a picture to get it right) > > -- > Jay A. Kreibich < J A Y @ K R E I B I.C H > > > "Our opponent is an alien starship packed with atomic bombs. We > have > a protractor." "I'll go home and see if I can scrounge up a ruler > and a piece of string." --from Anathem by Neal Stephenson > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
