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

You have to make a distinction between attributes and entities. An entity is a thing - usually a noun. An attribute is more like a piece of describing information. In database jargon, entity = table, attribute = field/column.

Having a separate table for certain things, let's use director, as an example, is called normalizing. While it can be good in some circumstances, it can be unnecessary in others (as generally it makes queries more complicated - you have to join everything - and it is slower).

In this case, having a year table is unnecessary, since there are no other attributes about a year, besides the year itself, that you would store. It is better to denormalize this and store the year in the film table itself.

Director, on the other hand, is different. Perhaps you'll want to store the director's first name, last name, date of birth, date of death (if applicable), etc. You obviously don't want to enter the director's birth date every time you enter a film that this person directs, so it makes sense to have a separate entity for a director.

Even if you didn't want to store all this information about the director (you just want their name), having a separate table for it (and using a surrogate key - I'll get to that in a second) is useful because it prevents typographic errors and duplicates - if you have someone's name spelled wrong or entered differently (first,last vs last,first), then if you try to find other movies they've directed, you'll fail.

Using a surrogate key (primary key) for tables is generally a good idea. Matching an integer is much faster than matching a string. It also allows you to freely change the name, without worrying about the foreign keys stored in other tables (the ID stays the same, so you don't have to do anything).

---

You can really take this design quite far, and it's all a matter of figuring out what you want to be able to store in it.

For example, rather than have a single director per film, some films have multiple directors.. so there would be a many-to-many relationship between films and directors, so you'd need a table with eg:

films_directors => **filmid, directorid**

Taking it a step further, sometimes directors are also actors, and vice-versa. So rather than even have director and actor tables, you could have a single person table, and join that table in using a role table. The role table would hold various positions - eg, director, producer, star, extra, grip, editor.. and it would look more like:

films => **filmid**, title, otherstuff...
people => **personid**, name, ....
roles => **roleid**, role name, ....
film_people => **filmid, personid, roleid**
genre => **genreid**, name, ...
film_genre => **genreid, filmid**

You might also have a role_details field in the film_people table, which could contain extra information depending on the role (eg, the name of the part the actor is playing).

I'm also showing genre as a many<>many relationship, because possible a film is in multiple genres. If you didn't want this, then instead of the film_genre table, films would just contain a genreid.

Once this is set up, it is easy to query and find everything a given person has done, or everything a person has done as a director, or everyone who has ever directed a movie, or all the people involved with one specific movie.. It can go on and on.

Answer 2

What follows is not actual MySQL code. It seems like what you need is more of a conceptual start here. So here's a model of what your database should look like.

Actor table

• id (primary key)
• first name
• last name
• etc. (any additional columns you want to store on an actor)

Director table

• id
• first name
• last name
• etc.

Genre table

• id
• name
• etc.

Film table

• id
• title
• description
• running time
• release date
• director id -- this is a foreign key that refers to the id (the primary key) of the director who directed the film
• genre id -- like the director id, this refers to the id of the genre the film belongs to

Actor-film index table

• film id -- this is a foreign key that refers to the id of the film
• actor id -- this is a foreign key that refers to the id of one actor in the film.

For each actor in the film, you would add a row to the Actor-Film Index. So, if actors 5 and 13 (the primary keys for those actors) starred in film 4 (again, the primary key for that film), you'd have two rows reflecting that fact in your index: One with film id = 4, and actor id = 5, and another with film id = 4, and actor id = 13.

Hope that helps.

Also, this assumes that each film has exactly one director. If any film in your library has two directors (such as Slumdog Millionaire), you'd want to separate out the director id from the film table, and create a Director-Film index like the Actor-Film Index as above.