Unanswered: relationships: How do I set multiple sections per student?
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.
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