Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Christchurch
    Posts
    111
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi, I have started helping someone who is putting together his first Access database and has already set about creating tables for pupils, who have involvements at school (eg sports, clubs), and teachers who have different involvements at the school (eg teacher, coaching, committees). A record in tblPupils, based on primary key pupilID, has 1 to many relationship with tblInvolvements. A record in tblStaff, based on primary key staffID, has 1 to many relationship with tblStaffInvolvements.

    Moving to now think about classes, who were their class mates, who were their teachers, over different years. And for teachers, who did they teach. Seems this is requiring a many-to-many relationship but I am unsure how to structure this, is there only one joining table needed between tblStaff and tblPupils? What fields should be going in that table - just fields for the primary keys from tblStaff and tblPupils? There would need to be fields for "rooms" and "years" - is this in the joining table, or in both the tblStaff and tblPupil tables?

    Thank you for your assistance,
    Roger

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You don't want year and room fields in tblPupils, nor in tblStaff, because they are not properties of pupils or staff.

    You might use a join table with PupilID, StaffID and Year as fields for a composite unique key, plus additional info if needed.

    Or if pupils are organized in groups that stay together during a school year, you might define a tblGroups with a group ID as primary key, and fields such as year. You can then have a table that joins pupils and groups, and another table that joins staff and groups.

  3. #3
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Christchurch
    Posts
    111
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='786033' date='24-Jul-2009 13:16']You might use a join table with PupilID, StaffID and Year as fields for a composite unique key, plus additional info if needed.[/quote]

    Thanks for the reply. In this situation the pupils will stay together for the year in the same room so I think the composite primary key based on those plus the PupilID fields will be OK.

Posting Permissions

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