> 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.
__________________________________
Do you Yahoo!?
Yahoo! Mail - Find what you need with new enhanced search.
http://info.mail.yahoo.com/mail_250
