Results 1 to 3 of 3
  1. #1
    Join Date
    May 2003
    Posts
    2

    database design question for college database

    Hi all,

    I'm fairly green at database design and have come across a situation where I'm not sure of the pros/cons.

    I'm designing a database that contains information about a particular college. The entities I've established so far are: department, course, class, student and instructor.

    The problem/question I have is in regards to composite keys. For instance my StudentClass table (shows students enrollment in particular classes) has a composite key made up of 5 primary keys: studentid, departmentcode, semestercode, coursenumber and sectionnumber. This seems excessive to me and was wondering what type of performance issues I'll have with that many columns designated as primary keys??

    Also, I was wondering if it makes sense to assign an arbitrarily unique number to represent a particular class. For instance, a class with the following values would be assigned a 1 (arbitrary integer):

    DepartmentCode: TTT
    SemesterCode: 20028
    CourseNumber: 150
    SectionNumber: 000

    If the above makes sense I think I would be much easier when assigning students to classes such that I could have a linking table with the student's unique identification number and the unique arbitrary integer that represents a class. Instead, as of now I'd have to represent a StudentClass with the following values: studentid, departmentcode, semestercode, coursenumber and sectionnumber. It just seems to me that I'm repeating those values un-necessarily throughout the database. Does that make sense??

    Thanks a bunch!

    Ryan

    Hope all this makes sense

  2. #2
    Join Date
    Apr 2003
    Posts
    191

    Re: database design question for college database

    Hi Ryan,

    if you think that those 5 columns make up a primary key go ahead and use them.

    Of course it is easier to write applications that don't need to update primary key columns if you use a short surrogate key, and many joins will actually benefit from small keys, but I doubt it is worth the trouble to settle for a suboptimal relational model if you don't really need the surrogate key. I would try and keep the model clean in the first place.

    Just my 2 cents,

    Johann

    Originally posted by rdaedlow
    Hi all,

    I'm fairly green at database design and have come across a situation where I'm not sure of the pros/cons.

    I'm designing a database that contains information about a particular college. The entities I've established so far are: department, course, class, student and instructor.

    The problem/question I have is in regards to composite keys. For instance my StudentClass table (shows students enrollment in particular classes) has a composite key made up of 5 primary keys: studentid, departmentcode, semestercode, coursenumber and sectionnumber. This seems excessive to me and was wondering what type of performance issues I'll have with that many columns designated as primary keys??

    Also, I was wondering if it makes sense to assign an arbitrarily unique number to represent a particular class. For instance, a class with the following values would be assigned a 1 (arbitrary integer):

    DepartmentCode: TTT
    SemesterCode: 20028
    CourseNumber: 150
    SectionNumber: 000

    If the above makes sense I think I would be much easier when assigning students to classes such that I could have a linking table with the student's unique identification number and the unique arbitrary integer that represents a class. Instead, as of now I'd have to represent a StudentClass with the following values: studentid, departmentcode, semestercode, coursenumber and sectionnumber. It just seems to me that I'm repeating those values un-necessarily throughout the database. Does that make sense??

    Thanks a bunch!

    Ryan

    Hope all this makes sense

  3. #3
    Join Date
    May 2003
    Posts
    2

    Re: database design question for college database

    Thanks Johann! I just wasn't sure how a 5 column primary key would effect query performance on SQL Server 2000 if at all.


    Originally posted by jsander
    Hi Ryan,

    if you think that those 5 columns make up a primary key go ahead and use them.

    Of course it is easier to write applications that don't need to update primary key columns if you use a short surrogate key, and many joins will actually benefit from small keys, but I doubt it is worth the trouble to settle for a suboptimal relational model if you don't really need the surrogate key. I would try and keep the model clean in the first place.

    Just my 2 cents,

    Johann

Posting Permissions

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