DB Scheme for movie jukebox

Hi,

I am trying to create a Movie Jukebox for an embedded device (Media player) and sqlite seem like a good candidate to do the job unfortunately my knowledge of SQL is very limited although I have been doing a lot of reading lately.

My current scheme for the Database is quite simple but I think that it is very inefficient and only uses one table:

Movie=(title, genre, year, poster(jpg_path), info(jpg_path), file(moviefile_path);

Since genre can be many for one Movie (typically 'Action, Science Fiction, Adventure' for one entry) I want to isolate it from the rest of the data and this is what I think would work best .... well maybe I don't know!

Movie=(movie_id (Pk)(auto inc), title, year............)
gnere=(genre_id (PK)(auto inc), genre_name)
movie_genre=(gnere_id (FK), movie_id (FK))

Data will be selected using Year or genre; therefore I figure it would be faster to look thru the small genre list pull the genre_id then I only work this integer primary keys to pull the rest of the record. I think that this will work:

SELECT * FROM Movie WHERE movie_id IN
(SELECT movie_id FROM movie_genre WHERE genre_id = (SELECT genre_id FROM Genre WHERE genre_name = '$GENRE_VAR'));

Giving $GENRE_VAR of Action all the movies having a genre category of Action will be pulled out of the DB.

All that being said; here's my first question:

Is my assumption about the use of 3 tables compare to 1 really a time saver given that I need to create 2 extra sub table before I eventually get to the data I want.

My second question is a little more complicated to me anyway since I do not know what I am doing. The Records are based upon a scan movie list and various data being extracted from an nfo file containing specific information about the movie (year, genre, director etc.....). Given the schema with 3 tables all do I keep all the data coherent amongst the 3 tables. I mean I can insert/update/replace data in the movie table fairly easy but how do I fill the other tables????

INSERT Movie VALUES ('title','poster' ......);
INSERT genre IF NOT EXIST VALUES ('$GENRE_VAR);
INSERT movie_genre VALUES (????? I have not idea);

Keep in mine that all this is done using a linux shell CGI script; and sqlite (command line); not that it really matter I don't think anyway.

Any comments / suggestions would be greatly appreciated.

Please keep it real simple you are dealing with a real noob here with regards to SQL.

Thanks.

Your three table design is most correct.

As far as insertion goes, databases that support autoincrement fields generally have a function that lets you retrieve the ID of the last inserted row. I don't know specifically what it's called in SQLite, but it's probably something like INSERT_ID().

I look at the documentation for sqlite and I think that what I need to use is the "last_insert_rowid()" function to do this. So I am guessing that things will look something like that:

sqlite movie.db "INSERT Movie VALUES ('title','poster' ......)";
M_id=sqlite movie.db "last_insert_rowid()";
While read Line
do
sqlite movie.db "INSERT or Ignore Genre VALUES ('$GenreName')";
G_id=sqlite movie.db "last_insert_rowid()";
sqlite movie.db "INSERT movie_genre VALUES (G_id,M_id)";
done < genre_movie_list

I think that should work but I wonder if I declare the GenreName UNIQUE if the "last_insert_rowid()" ; will return the rowid() of the genre that already exist in the table ????

Man this is definitely more complicated than one big table but probably more efficient especially for those with thousand of movies.

Thank you for pointing me in the right direction, really appreciate it.

Snappy46

It won't, you need to look up the ID yourself.

Arghhhh!!! I was afraid of that; thank you so much for your help!!!

Cheers!!!