> > > In my model, a 'song' record consists of a unique artist/album/title > > A 'tag' is a name/value pair associated with a 'song': > > Song: song_id artist album title > > Tag: song_id name value > > Searching for the tags associated with a given song would be pretty > > easy, but complicating matters I need for tags to 'cascade'. > > > > This is so that a tag can be set for an entire album, and then > > overridden if necessary on a song by song basis. For example, if I am > > checking the value of the tag 'rating' for Artist/Album/Title, but no > > such tag is found, I want to fall back on the tag for > > Artist/Album/NULL; failing that I search for Artist/NULL/NULL. > > > > As I see it, this means that I need to be able to search the tags > > table by three different song_id's (id_exact, id_album, id_artist). I > > can come up with several ways of doing this: joins, subselects, or > > unions. For example, the join approach would need to do an outer join > > between the song and tag tables three times, once on song_id=id_exact, > > once on song_id=id_album, and finally on song_id=id_artist. > > > > (note that if a tag exists for id_exact, it overrides the results for > > id_album and id_artist: the cascade is conditional on no tag found.) > > > > It strikes me that this is probably going to be pretty inefficient. > > My requirements are not for incredible speed, but I probably need to > > be able to search through a database of 10,000 songs and 100,000 tags > > in something less than a tenth of a second on a typical desktop PC. > > > > Is there a way of approaching this that works better with SQLite than > > other ways? > > If I understand correctly, the "join approach" that you suggest is > probably the only one in which indices will be used. So maybe it's > the best.
Actually that's not right, there's no reason correlated sub-selects would not use indices too. Sorry :) __________________________________ Do you Yahoo!? Yahoo! Mail - You care about security. So do we. http://promotions.yahoo.com/new_mail