How to design a movie database?

Question

I'm trying to get my head round this mind boggling stuff they call Database Design without much success, so I'll try to illustrate my problem with an example.

I am using MySQL and here is my question:

Say I want to create a database to hold my DVD collection. I have the following information that I want to include:

1. Film Title
2. Actors
3. Running Time
4. Genre
5. Description
6. Year
7. Director

I would like to create relationships between these to make it more efficient but don't know how.

Here is what I'm thinking for the database design:

Films Table => filmid, filmtitle, runningtime, description

Year Table => year

Genre Table => genre

Director Table => director

Actors Table => actor_name

But, how would I go about creating relationships between these tables?

Also, I have created a unique ID for the Films Table with a primary key that automatically increments, do I need to create a unique ID for each table?

And finally if I were to update a new film into the database through a PHP form, how would I insert all of this data in (with the relationships and all?)

thanks for any help you can give, Keith

Answer 1

Every table should have a primary key which is unique.

You should read up on database normalization.

A year table is probably unnecessary.

If it's year of release, say, then the year can be stored in the film.

If there are multiple directors on a film, then you would have a separate table which would hold the primary key of the film table and the director table. Similarly for any of the foreign key constraints which are many-to-one or many-to-many. In particular, I believe this would apply to the Actor.

Answer 2

Sometimes actors are directors and vice versa, maybe you want a "people" table?

Answer 3

I have created a unique ID for the Films Table with a primary key that automatically increments, do I need to create a unique ID for each table?

Yes, each table must have a unique id. But, that's not necessarily the primary auto incrementing key - it's whatever makes that particular instance unique. For instance, for movies, I think it's common to be title + year of release - though you'd want to check with a movie buff (a domain expert) to be sure of that. The auto increment is a fallback - basically, when you really don't have anything else to uniqueify on.

You may use an auto increment key for ease of use in joins and such, but you should have a unique constraint on the uniqueness fields anyway.

As for the actual design, I'd suggest something like:

Films => Primary Key(filmid), Unique Constraint(filmtitle, year), 
         runningtime, description, 
         Foreign Key(Genre), Foreign Key(DirectorId)

Genre Table => Primary Key(Genre)

Director Table => Primary Key(DirectorId), DirectorName

Actors Table => Primary Key(ActorId), ActorName

Films_Actors => Primary Key(Foreign Key(ActorId), Foreign Key(FilmId))

For the insert, well - frankly, it's a PITA. You need to insert in reverse order (and this is where auto increment keys can be an even bigger PITA - if you can add date of birth or something into the Actors and Directors table, then a unique constraint can make it easier).

So, you'd insert Actor(s), Director, Film, and then Films_Actors. Ideally, all in a single transaction. Also, I assume Genre is already filled in, and is a select list - so it doesn't need to be inserted.

Answer 4

You can download Imdb schema here.