Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2006
    Posts
    8

    Unanswered: Combined/Composite Key vs. new field - good/bad?

    I am creating a database with a number of tables, including some similar to these three...

    Student(StudentNo, FName, SName, CourseNum)
    StudentMod(StudentNo, ModNo, Mark)
    Mod(ModNo, MName)

    As students have many mod(ule)s the StudentMod table would require both StudentNo and ModNo to make the records unique.

    StudentNo, ModNo, Mark
    1 , 55 , 55%
    1 , 67 , 70%
    2 , 55 , 62%

    The above is a sample.

    I was considering therefore having....

    StudentMod(SMNo, StudentNo, ModNo, Mark)

    Creating a new field to store a primary key, ridding of the need of two fields to identify the record.

    But, as this is not used elsewhere - isnt that pointless?

    Which is the best practise?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, it is pointless

    best practice is the composite primary key
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2006
    Posts
    8
    Thank you.

    I was adamant I was right not to create a new field but then someone said I had to because it was bad practise!!

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if this someone is your manager, you might want to just play along

    few things annoy a manager more than being wrong and having this pointed out by a subordinate

    if this someone is not your manager, tell them flat out they are wrong, and have them contact me for the reasons (contact details available through my web site)
    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
  •