Simple database design question

[suki]

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

 

[suki]

thanks for the replies, i will have a go at some changes and report back

thanks again

 

[suki]

Damn, i was longer looking at that than i thought.

thats not a problem with me
are there any other foreign keys i am needing

updated diagram - thanks for help again

 

[suki]

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 (above)


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

 

[suki]

yes this is the usual way to do it



what you have in the joining table (CAST) is what's known as a composite primary key and it's just fine as it is. you can remove the surname and the movei title as you suspect. you don't need to store the info twice. pull those out and I think you'll be in 3nf

personally I'd pull the interviewer, the director, and the details of the actor into an aditional table called people and just store the FKs here but that's up to you

HT

thanks very much for your help