Simple database design question

suki

suki

suki

Associate
Joined
31 Oct 2003
Posts
526
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

diagram.JPG
 
I'm not a DB wizard by any stretch of the imagination but i'll have a go at a couple of observations.

The attribute Interview Successful that appears in 2 different tables. Is that the same attribute or a different one with the same name? If it is the same attribute then you only want to see it once.

Location appears twice but i'm assuming they are 2 different attributes, might want to name them as such.

What significance does Job Role have?

Why do you need Cast ID? What is the whole Cast table supposed to do? Does your ER model say that a movie can have more than one cast or should it be cast members?

Memoir does not appear to be related to the rest of the model from what i can see. Maybe it is but i'm assuming that title in Memoir refers to the title of a memoir and the title in Actor refers to Mr/Miss type thing? I'd stick Actor ID into Memoir as a foreign key.

In Memoir, would title and date created not be better as the key? What's the chances of a memoir with the same title coming out on the same day? Slim. If you can, avoid arbitrary numbering for keys unless it makes real sense to do so. Eg. An employee number. That's maybe something that can be applied to more than one of your tables.

If you state your assumptions then perhaps someone else can help you better as it's a little confusing at the moment. Hope i've helped more than hindered.
 
Lovemonk said:
Damn, i was longer looking at that than i thought. :D
Click to expand...



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

updated diagram - thanks for help again

diagram.JPG
 
Last edited:
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 said:
hi
thanks again for the pointers

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

yes this is the usual way to do it

suki said:
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
Click to expand...

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
 
happytechie said:
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
Click to expand...

thanks very much for your help
 
You must log in or register to reply here.
Back
Top Bottom