Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2008

    Unanswered: relationships: How do I set multiple sections per student?

    Hello all,

    I'm hoping that someone can point me in the right direction about this:

    My concern is between two tables, SectsTbl and StudsTbl (named as such for the sake of this example =p) and how to create a second relationship between them.

    In detail:

    SectsTbl's PK field is "sec_code" which appears as a foreign key in StudsTbl. It appears as field "currentSect". StudsTbl's PK field is "SID".

    Using this setup, I can 'enroll' multiple students under a single class. My problem is that I want to enroll a student into multiple sections -and still keep all existing functionalities I've made.

    The first idea I have is to create a new table using SID as a pk field, and seven more fields for every section a student is enrolled under.

    Instead of the SectsTbl and StudsTbl being directly related, the new table would serve as a way to link to two tables. Well, in theory anyway. I tried making it, but I do not know what should serve as a pk or foreign key in which table and exactly what kind of relationship option I should choose.

    I chose seven sections as the limit because a student would, at most, enroll in four sections; and on average, enrolls in one or two sections. having seven possible sections enrolled under is a extremely safe limit.

    Either way, there's a good chance that I'm going about this the wrong way. If anyone has any suggestions on how to make the above solution work, or alternatives I can take, that would be totally awesome. Thanks! =D


  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    this is a classic many-to-many relationship

    remove currentSect from StudsTbl table table

    add a new table called StudSects, with exactly two columns: SID and sec_code

    if a student is enrolled in 4 sections, there are 4 rows for that student in the StudSects table

    the two columns are each a foreign key back to their respective table, thus creating the many-to-many structure, while the two columns together are a composite PK

    simple, yes? | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2008
    Thanks! that sounds easier than what I mentioned. I'll see how I can get a form to populate this table.

Posting Permissions

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