Results 1 to 4 of 4
  1. #1
    Join Date
    May 2006
    Posts
    7

    confused with foreign keys

    Hi friends,

    I am a little confused with some basic basics.

    I am making a database where I have tables that I want to discuss;
    Subjects
    SubjectsLkp
    BwSubjectsTaught
    Teachers


    Business Logic:
    1) I want teachers to be able to select subjects and their description from a subject listing. I have put these subjects (names and descriptions) in columns in a lookup (descriptive) table. Its called SubjectsLkp.

    2) I want to be able to sort teachers according to the subjects they teach. There are many teachers and many subjects. A many to many relationship is normalised via a bridging table called BwSubjectsTaught.

    I want to clarify my foreign keys in the teachers table;
    Do I add foreign key (it would be a lookup value for SubjectsLkp) assuming that one record for a teacher gives one subject name?
    Do I add a foreign key which references a bridging table so that I can sort subjects against Subjects via BwSubjectsTaught?

    There is a distinct difference in the methodology between lookup (descriptive) tables and the normalisation of many to many relationships, and the use of foreign keys would be different in the two cases?

    Thankyou

    OxyJen

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by OxyJen
    I want to clarify my foreign keys in the teachers table;
    Do I add foreign key (it would be a lookup value for SubjectsLkp) ...
    no, there should be no foreign keys in the teacher table

    there should be one foreign key in the BwSubjectsTaught table that references the teacher table, and another that references the subjects table

    together these two foreign keys will comprise the composite primary key of the BwSubjectsTaught table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2006
    Posts
    7
    "there should be one foreign key in the BwSubjectsTaught table that references the teacher table, and another that references the subjects table

    together these two foreign keys will comprise the composite primary key of the BwSubjectsTaught table"

    I have the composite key already in the bridging table
    It does comprise of foreign keys and has the suffix CoK
    eg BwSubjectsTaughtCoK
    Do I need to also add separate relationships in there eg
    BwSubjectsFK
    BwTaughtFK
    or does the composite handle the relationship for you?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sorry for the delay in replying, my computer died and i had to wait for the new one to be shipped

    if you would like more help with your keys, could yo uplease show the columns in each table and what they're for...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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