hi,

i am designing quite a simple database for the first time and just want to make sure i am doing it correctly.


can someone tell me if this would work please

my main worry is that i dont know if i have the correct primary keys in the correct places(such as composite keys,compound keys)

thanks

my diagram - http://suki.vidahost.com/diagram.JPG

You wouldn't need a Cast_Id in the cast table. This table is an example of an intersection table, used to create a many-to-many join. It's primary key is a compound PK, consisting of BOTH the foreign keys that you've added. (ActorID and MovieID)

You are probably correct in having the interview associated with the actor, rather than the cast, as an interview could be associated with multiple movies for a given actor,

hi

i've updated the diagram

do i need any other foreign keys ?

thank you

Well, in the interview table, you have a foreign key missing.

In the cast table, you have two foreign keys missing.
You might also want to add a "role" text field there...

hi

what would you suggest these foreign keys to be ? i am new to databases and finding it quite hard.

will the foreign keys be the primary keys of other tables ??
how would i figure out when i foreign key is needed??


thank you again
much appreciated

Yes
They're needed whenever you wish to relate one table to another. In the case of an intersection table like cast, you need more than one foreign key, as you are relating CAST to MOVIE **AND** CAST to ACTOR.

hi
thanks again for the pointers

is it possible for a primary key to be a foreign key in the same table ??

i've updated the diagram again if you could take a look

http://suki.vidahost.com/diagram.JPG

so i was just wondering if in the CAST table i need to have Surname and Movie title as the FK's or could i just not use the PK's.
Wouldnt have Surname and Movie in the CAST table mean duplicate data ??

thanks you helping

can an actor appear in more than one role in any one film, if so your model will fail

no they cannot.

That's fine, but be aware, in the real world, they can, and often do. As a classic example, in the original "Parent Trap", Halie Mills played twin sisters.