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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
