On 08/02/2011, at 10:19 AM, Yuzem wrote: > I have the tables "movies" and "people" > Those two tables are related by tables "directors", "writers", etc... > movies: id, title > people: id, name > > directors: movieId, peopleId > writers: movieId, peopleId > etc... > > How can I normalize that so I can count directors and writers using > count(*)? > Should I create another table for "directors" and another for "writers", > etc... with only the ids? > I will end up having: > movies > movies_directors < (directors people)
After implementing the schema in my previous post, add this: begin immediate; create table People ( ID integer primary key not null , Name text collate no case not null unique ); create table Capacity ( ID integer primary key not null , Name text collate no case not null unique ); create table "Movie People" ( ID integer primary key not null , Movie_ID integer not null references Movies (Movie_ID) on delete cascade , Capacity_ID integer not null references Capacity (ID) on delete cascade , People_ID integer not null references People (ID) on delete cascade , unique (Movie_ID, Capacity_ID, People_ID) ); commit; Then you can count the directors like this: select count(distinct People_ID) from "Movie People" join Capacity on "Movie People".Capacity_ID = Capacity.ID where Capacity.Name = 'director'; or: select count(distinct People_ID) from "Movie People" where Capacity_ID = (select ID from Capacity where Name = 'director'); or you can create a view to alphabetically list each director with a CSV list of their movies, like this: create view "Directors" as select People.Name, group_concat (Movies.Title, ', ') from "Movie People" join Movies on "Movie People".Movie_ID = Movies.Movie_ID join Capacity on "Movie People".Capacity_ID = Capacity.ID join People on "Movie People".People_ID = People.ID where Capacity.Name = 'director'; group by People_ID order by People.Name ; 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