Sorry your browser is not supported!

You are using an outdated browser that does not support modern web technologies, in order to use this site please update to a new browser.

Browsers supported include Chrome, FireFox, Safari, Opera, Internet Explorer 10+ or Microsoft Edge.

Geek Culture / database query question

Author
Message
Phaelax
DBPro Master
22
Years of Service
User Offline
Joined: 16th Apr 2003
Location: Metropia
Posted: 23rd Oct 2012 00:52
I have a table containing movie title and a field for actors. The actor data would look like this: "5,18,32,12". It would be a series of indices to the actor database, where each number pertains to a different actor id.

As a movie is added to the database, each actor (typically no more than 5 per movie) is queried against the actor table to see if it exists. If so, grab the ID to add to the actor string. If not, add the new actor to the actor table and grab that new id.

My question is, is this a good method for storing the information? Every time a movie is added, the actor table could get hit with up to 5 additional queries, plus anywhere from 0 to 5 insert queries. And I don't want to store a list of actor names within the movie table because that's just not good normalization.

"You're not going crazy. You're going sane in a crazy world!" ~Tick
ionstream
20
Years of Service
User Offline
Joined: 4th Jul 2004
Location: Overweb
Posted: 23rd Oct 2012 01:09 Edited at: 23rd Oct 2012 01:11
Use a table that consists of a movie id and an actor id, and each record signifies that that actor was in that movie. Example:

Movies:
id,Name
1,Titanic

Actors:
id, Name
1, Kate Winslet
2, Leondardo DiCaprio
3, James Earl Jones

Movies_Actors
Movie ID, Actor Id
1, 1
1, 2


That way you can do a single query to get all actors in a movie:

SELECT actors.name FROM actors, movies_actors WHERE movies_actors.actor_id=actors.id AND movies_actors.movie_id=1

This does a join on actors, and movies_actors, matching by actor id.

You can also do the same to get all a movies an actor has been in:


SELECT movies.name FROM movies, movies_actors WHERE movies_actors.movie_id=movie.id AND movies_actors.actor_id=1


Be sure to make the right indexes, typically Foreign Keys, on the Movies_actors fields.

MrValentine
AGK Backer
14
Years of Service
User Offline
Joined: 5th Dec 2010
Playing: FFVII
Posted: 23rd Oct 2012 01:16
ionstream beat me to it, but you could possibly also look into Serialising Variables and Objects had a quick look, but very tired right now to read further into it, sorry...

the_winch
22
Years of Service
User Offline
Joined: 1st Feb 2003
Location: Oxford, UK
Posted: 23rd Oct 2012 02:34
I think the easiest way to answer these type of questions is to work out what the demands on the database will be in terms of inserts, updates and selects.

In this case how many films will they be and what rate do they need to be inserted into the database?
Probably there are not a huge number of films and most of the time will be spend getting the data and formatting it so it can be inserted into the database. Then once the data is in there it isn't going to be modified very often.

On the other hand answering queries like, which actors started in movie x or which movies has actor y stared in are going to be the main function of the database.

With your schema how would you find all the films an actor stared in? You would first have to look up the actor in the actors table to find the id. Then you have to find all the movies with that actor id. That's a bit complected as the actors are stored in a string so you would have to look at every movie row and parse the actor string.
That's much more of a worry than having to check if an actor already exists in the actors table every time a movie is added.

By way of demonstration, he emitted a batlike squeak that was indeed bothersome.
Phaelax
DBPro Master
22
Years of Service
User Offline
Joined: 16th Apr 2003
Location: Metropia
Posted: 23rd Oct 2012 03:46
Movies (DVDs, VHS, etc..) will be scanned by use of a barcode scanner, the data retrieved through amazon, then finally inserted into the database to keep record of everything I (or another user) owns.

I can't really provide a link to the working site until I know for sure my developer license with amazon allows public use of my app in this manner.

Good idea ionstream. And that got me think about other things as well. I think I need to draw some UML figures and map this stuff out a little bit.

"You're not going crazy. You're going sane in a crazy world!" ~Tick
Indicium
16
Years of Service
User Offline
Joined: 26th May 2008
Location:
Posted: 23rd Oct 2012 07:54
ionstream: How would one go about seeing if two actors are both in the same movie? I have a similar problem.


They see me coding, they hating. http://indi-indicium.blogspot.co.uk/
Phaelax
DBPro Master
22
Years of Service
User Offline
Joined: 16th Apr 2003
Location: Metropia
Posted: 23rd Oct 2012 09:07
That's easy if you know what movie you want to look at (myId):

SELECT actor_id FROM Movies_Actors WHERE movie_id = myId


I redesigned the entire DB, now I have 10 tables versus the 2-3 I was trying to work with.

"You're not going crazy. You're going sane in a crazy world!" ~Tick

Login to post a reply

Server time is: 2025-05-17 12:40:11
Your offset time is: 2025-05-17 12:40:11