the Many to many problem

[saleemMSMS]

heres the scenario.
a training program has sessions. these sessions are attended by 2 different groups of people. one group can attend more than one session while a session can have more than one group of attendants.

i have 3 important entities for this matter.
session entity has [sessionId(PK), duration, title and datetime]
StudentGroups [studentGroupID(PK), groupName, LevelOfExp)
Professionals [ profID (PK), name, rank, baseDistrict]

so there are m:n relationships between session entity and the following 2 entities. i cannot take the student groups and the professionals under a superclass since their IDs (primary keys) are in different format.

so these are the tables i got after normalisation

session[sessionId(PK), duration, title and datetime]
StudentGroups [studentGroupID(PK), groupName, LevelOfExp)
Professionals [ profID (PK), name, rank, baseDistrict]
StudentAttendance [sessionID, studentID (Composite PK)]
ProfessionalAttendance [profID, studentID (composite PK)]

When the number of entities increase, the number of "attendance" tables also increases worrying me. is there a way i can have all the attendance info in one table ?
please comment

 

[HiTechCoach]

is there a way i can have all the attendance info in one table ?

Yes. That would be the correct way to do it.

Without seeing all of your tables and the relationships, it will be hard to tell if you have it properly normalized. My guess would be: probably not.

It would help if you would post a sample database with your tables and some sample (test) data. Nothing confidential.

 

[gemma-the-husky]

the concept of student groups is interesting

what if a student is absent, and doesnt attend a meeting intended for his group

normally you would get

students_table
student_groups_table

now if a student can be in only group then you can hold this in the students_table
if he can be in more than 1 group, then you need a

student_group_membership_table

now when it comes to a meeting you would normally register a student for the meeting - not the studentgroup.

its up to you of course.

the same principle applies to tutors/tutorgroups.


now you have a
meeting_table

and a
meeting_attendance_table

so whether you register the students indivdiually, or the student_groups depends on what you want to do

 

[saleemMSMS]

Yes. That would be the correct way to do it.

Without seeing all of your tables and the relationships, it will be hard to tell if you have it properly normalized. My guess would be: probably not.

It would help if you would post a sample database with your tables and some sample (test) data. Nothing confidential.

i'll post a sample copy ASAP

 

[saleemMSMS]

the concept of student groups is interesting

what if a student is absent, and doesnt attend a meeting intended for his group

normally you would get

students_table
student_groups_table

now if a student can be in only group then you can hold this in the students_table
if he can be in more than 1 group, then you need a

student_group_membership_table

now when it comes to a meeting you would normally register a student for the meeting - not the studentgroup.

its up to you of course.

the same principle applies to tutors/tutorgroups.


now you have a
meeting_table

and a
meeting_attendance_table

so whether you register the students indivdiually, or the student_groups depends on what you want to do

well, i do not need to keep track of each and every student of a particular group.. this is about groups. there may be many kinds of student groups and also many kinds of professional groups. what matters is the attendance of the group in the session.
the problem is we cannot keep a field as foreign key in the session table since if its a student group, the profID(FK) wont get filled while if its a Professional Group, the StudentID(FK) of the session table wont get filled.
its kind of odd to allow null values in a foreign key. thats why i'm wondering what to do.

 

[The_Doc_Man]

I think this comes down to the issue of synthetic vs natural keys.

If the ID fields are wacky-different, then don't use them. Generate a synthetic key via autonumber in BOTH tables and use that as the PK. Then your junctions can use a UNION query on the synthetic key where the "real" keys are now just data fields of an arbitrary text format. But you can leave them uniquely indexed for searches within the individual tables if you wish. Union queries give you a method to "remap" different tables, some apparently disparate. The only problem you would have is that the union query might not be updateable, to the point that you might have to write some extra code in VBA to do separate recordset-level updates.

 

[gemma-the-husky]

ok, you have obviously considered it, and group is the atomic level for what you are trying to do.

in that case it seems to me that its possibly a normalisation thing

lets say you have a meeting, and the rule is that a meeting can be attended by

a) one professional group only or
b) multiple professional groups

(i mean generally, not a particular meeting - its either single or many as a principle)

AND

c) one student group only
b) multiple student groups

now in the meeting table

if a) then you can store this foreign key in the table
if b) you will need another table to store the meeting/professional groups list

same with students

if c) then you can store this foreign key in the table
if d) you will need another table to store the meeting/student groups list

if the meeting is private to one group or another (ie just profs, or just students) just leave the relevant fk blank

if its important to distinguish between a blank entry, and a not required entry, then you may need to add an appropriate flag to the meeting table.

does this help?