Results 1 to 10 of 10

Thread: Design Help

  1. #1
    Join Date
    Mar 2006
    Posts
    5

    Design Help

    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

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Quote Originally Posted by suki2shoes
    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,
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  3. #3
    Join Date
    Mar 2006
    Posts
    5
    hi

    i've updated the diagram

    do i need any other foreign keys ?

    thank you

  4. #4
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Quote Originally Posted by suki2shoes
    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...
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  5. #5
    Join Date
    Mar 2006
    Posts
    5
    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

  6. #6
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Quote Originally Posted by suki2shoes
    will the foreign keys be the primary keys of other tables ??
    Yes
    Quote Originally Posted by suki2shoes
    how would i figure out when i foreign key is needed??
    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.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  7. #7
    Join Date
    Mar 2006
    Posts
    5
    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

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    can an actor appear in more than one role in any one film, if so your model will fail
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Mar 2006
    Posts
    5
    no they cannot.

  10. #10
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Quote Originally Posted by suki2shoes
    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.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •