On Feb 7, 2011, at 9:32 PM, Yuzem wrote: >> Hey... sounds like IMdb :P >> > Yes, I'm coding a http://yuzem.blogspot.com/p/figuritas-screenshots.html > movie manager that grabs the info from imdb.
A fine hobby :) > I thought sqlite didn't handle foreign keys correctly, I thought wrong :) http://www.sqlite.org/foreignkeys.html > I am using triggers > to automatically delete tags when a movie gets deleted. Don't bother. > If I use a foreign key will it automatically remove the tag if the movie > gets deleted? See above. > Anyway, to use integers in the "tags" table is very complicated Nah... it's called normalization... it's a good thing :) http://en.wikipedia.org/wiki/Database_normalization > because I will have to assign the corresponding number to each tag that I > insert Yep. It's a good thing. > and I > have to insert lots of keywords for every movie. IMdb contains ~3.5M movie-keyword pairs (~405K distinct movies, 107K distinct keywords). Not much to write home about. > Does sqlite has any function to convert a text string into an unique number? You do it the other way around: create a unique entry in your tag table, then use the tag id in the movie_tag table that join movies to tags. Think of it as compression: instead of storing, say, the literal "accidental-cannibalism" multiple times in your movie_tag, you store the literal only once in the tag table, than use the literal id in the join table. And they lived happily ever after third normal form :)) _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
