Movie database design

[Cronos]

Hi

After my successful first post, I am tempting my luck with a database question.
I want to make a movie database (along the lines of IMdB).

I am thinking like this:
Table Movie
{id, Title, genre (from Genre), actors (from Persons), year, running time, director (from Persons), writer (from Persons), country (from Country), rating, keywords?}

Table Persons
{id, Name}

Table Cast (to connect Persons to roles in Movies)
{movieid, personid}

Table Genre
{...}

Table Country
{...}

Table Keywords ? (not sure about this one)
{...}

Genre and Country are removed from Movie itself, because I figured one movie may have many genres, and one genre can occur in many movies. Same for Country. How to connect them is still uncertain, though.

What are your thoughts on this design?

Please forgive my clutterings since I am not trained in databases at all. I do however understand the basic concepts, and figured the best way to learn was to start practicing.
I want to have a well designed database before I start.

Also, if this is not the right forum, please direct me to whereever I may ask my database questions.

[djr33]

You're in the right place.

Well, this is a complex design, but databases are, though not intuitive, very simple.


There are three ways to connect data together:
1. In the row for A in table 1, you will reference row B from table 2.
2. Row A from table 1 will be referenced in row B on table 2 [inverse].
3. Row C, by simply existing in table 3, has a value.
4. In Row D, within table 4, will contain a value in some cell that needs no linking (ex: a number).
(Note that A-D,1-4 are not more valuable or 'higher' than one another, just using different values for clarity.)

Though this may appear to be overanalyzing, it is crucial, because databases are not, inherently, linked together-- your PHP code and MySQL queries will link them, but you must know precisely how to search and link data. Obviously the cast for your film belongs to your film, but how exactly do you find your cast from that film?


First off, obviously you will have a table of "movies" or "films", each with data.

Also, it is good practice to have both a name and an ID so that you can change the name and only need to deal with a constant (and arbitrary) ID. Set this, for probably every table, as primary key and auto increment.


Examples:

This assume that you have already done one query and have the results as an array in $filmrow.

1. Your movie will have a value in one cell. That value links to another table's data.
SELECT * FROM `production_companies` WHERE 'ID'=$filmrow['PCo_ID']; (One result; the production company you wanted, AND you get an other info in that row, like where the company is based.)

2. Your movie will be referenced in that other table's row(s).
SELECT * FROM `actors` WHERE 'films' LIKE '%|$filmrow['filmid']|%'; (This returns a result of ALL actors that have the film listed in the films they've been in. The pipes around the name make sure it's part of a list, so you'll have IDs stored like "|2|6|13|" so that it won't think that 3 is in that list.)

3. A whole table of actors in your film (this method isn't efficient-- I don't recommend it.)
SELECT * FROM `actors_from_film_$filmrow['filmid']`;

4. Just grab a single bit of information:
SELECT `length` FROM `films` WHERE 'ID'=$filmrow['filmid'];

If this value happened to be a list, like perhaps actor_id's, then you could do a loop of queries to get them as well.


I hope this gets you started. Begin thinking about your general layout and how to link things together.

[Cronos]

Thanks, Daniel. I've read through your post a few times trying to get all information together.
I don't know, but I was planning on keeping this simple (on a level I can cope with in PHP/MySQL).

Obviously the cast for your film belongs to your film, but how exactly do you find your cast from that film?

With my scheme, I was thinking something like this:

SELECT name FROM person
 JOIN cast ON actorid=actor.id
 JOIN movie ON movieid=movie.id
 WHERE title="Lemony Snicket"

What I want to store about each movie is
Title, Actors, Genres, Writers, Director(s), Country, Running Time, Rating, Keyword

But I figured having everything in one table is not efficient. Therefore I took out Actors, Writers and Director(s) and made Persons.

Should I also make a separate table for Country, Genres and Keyword as well?
I will read your post over to figure out how to incorporate that into my scheme. Thanks.