Multiple keys for a table value?

Question

I’m sure my terminology is wrong, which is probably the reason google isn’t helping me. Anyways here is my problem and maybe you can deduce what my real question is...I hope

Ok let’s say I have a table called MOVIE this table has the values (columns?) for Name, ReleaseDate and Staring now my issue is how do i have the Staring value contain multiple values?

Do I make a separate table STARS with the columns Key, Actor1, Actor2 etc...? Where the Staring value from the MOVIE table points to the PK of the STARS table.

Key | Actor 1 | Actor 2 | .....
----------------------------------
01 | Bob Bob | John Bob | .......
02 | Jim Bob | John Bob | .......

However, The problem with this is that the table would be static and will need to have a preset number of actors.

OR

Is there a way to store multiple actorID’s (PK’s from the Actors table) in the Staring values from the MOVIE table? for example:

Name | ReleaseDate | Staring
-------------------------------------
blah | 1999 | 21, 46, 392, 182, 30
blah pt2 | 2001| 3, 11, 412

Anyways anything will be of help at this point and will be greatly appreciated.

Answer 1

In this kind of situation, you generally use 3 tables :

• 1 table for the actors data ; one line per actor ; primary key = id of the actor
• 1 table for the films data ; one line per film ; primary key = id of the film
• 1 table that acts as a link between those ; primary key = id of the film + id of the film

Here, it would mean :

• STARS
  • id
  • name
• MOVIE
  • id
  • title
  • release_date
• MOVIES_STARS
  • id_movie
  • id_star

And, to get a movie :

select * 
from movie
where id = X

And to get the actors in that movie :

select stars.*
from stars
    inner join movies_stars on movies_stars.id_star = stars.id
where movies_stars.id_movie = X

And you can even join to the movies table, if needed ;-)

A couple of keywords :

• Join table
• Many-to-many relationship

Answer 2

Usually, for a "many-to-many" type of relationship as is the case for Actors::Movies, you'd have a separate third table that looked something like this:

autoid | Actor | Movie
----------------------
 1        43      47
 2        36      22
 3        36      28
 4        17      47

where the values in the Actor and Movie columns of this table correspond to the primary key IDs of rows in the separate tables listing Actors and Movies. To get all of the actors for a movie with id 47, you'd query WHERE Movie = 47; to get all of the movies for an actor with id 36, you'd query WHERE Actor = 36, et cetera.

Answer 3

Ideally, the normalised solution is to have 3 tables, as follows:

TABLE Movie:
    MovieID (INT)
    Name (VARCHAR)
    ReleaseDate (DATE)

TABLE Actor:
    ActorID (INT)
    Name (VARCHAR)

TABLE Cast:
    ActorID (INT) 
    MovieID (INT)

Also, the columns are generally referred to as 'fields'

Answer 4

hmmm, this is a very basic normalization problem. from what you said, i can say that you movie relation is not in the First Normal Form. what you should do is to normalize it into 1NF, how? you create an weak entity set or relation, say, MOVIE_STAR, which has two attributes, the name and actor. then you should make both the name and actor as primary keys. note that i assume here that name attribute is the primary key of movie relation. i hope this helps... just a thought!