You have: CREATE TABLE movies(movie_id INTEGER, title TEXT, unique(movie_id)); CREATE TABLE tags(movie_id INTEGER, tag TEXT, unique(movie_id,tag));
You can solve your problem, using pure SQL. No need to resort to the application layer. Just execute the SQL transaction below. It takes care of everything, including normalizing, assigning integer IDs to unique tags, filtering our duplicates and auto indexes. I haven't tested on your tables specifically, but I do similar all the time. Any errors should be simple name errors. begin immediate; create temp table "Movies Old" as select * from Movies; create temp table "Tags Old" from Tags; drop table Movies; drop table Tags; create table Movies ( Movie_ID integer primary key not null , Title text collate no case not null ); create table Tags ( Tag_ID integer primary key not null , Name text not null unique collate nocase ); create table Movie_Tags ( ID integer primary key not null , Movie_ID integer not null references Movies (Movie_ID) on delete cascade , Tag_ID integer not null references Tags (Tag_ID) on delete cascade , unique (Movie_ID, Tag_ID) ); insert into Movies (Movie_ID, Title) select Movie_ID, Title from "Movies Old"; insert or ignore into Tags (Name) select distinct Tag from "Tags Old"; insert into Movie_Tags (Movie_ID, Tag_ID) select distinct Movie_ID, (select Tag_ID from Tags where Name = "Tags Old".Tag) from "Tags Old"; drop table "Movies Old"; drop table "Tags Old"; commit or rollback; Note that the last line "commit or rollback" is not an actual SQLite command. You will need to manually decide at that point whether to issue a commit or rollback. If there are any errors in previous lines, use rollback. Unfortunately, SQLite does not automatically rollback all commands in a transaction if one of the commands fails (specifically create and drop commands). Then you should be able to count tags very fast by: select count(*) from Tags; In order to make foreign keys work, you need to have SQLite version 3.6.19 or later, and use this pragma when you open a connection to SQLite (ie before any commands that require use of foreign keys): pragma foreign_keys = yes; A normalized database using integer keys is fast, small and elegant. Viewing integers by users is ugly. So for viewing the Movie-Tag combinations (and data entry), you might want to create a view to make it user friendly. It depends on your user interface. Reply here if you need that. Just something like this: create view "Movie Tags View" as select Movie_Tags.ID as ID , Movies.Title as "Movie" , Tags.Name as "Tag" from Movie_Tags join Movies on Movie_Tags.Movie_ID = Movies.Movie_ID join Tags on Movie_Tags.Tag_ID = Tags.Tag_ID ; Hope this helps, Tom BareFeetWare -- Comparison of SQLite GUI tools: http://www.barefeetware.com/sqlite/compare/?ml _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users