0

I have 3 tables that are currently defined as:

Current Rleationship

Is this the best way, or should I use an associative table?

I put the relationships backwards, 1 group can contain many users and many students. There is no other relationship between the user and the student other than the group. This is for a tracking program where the user or users are taking a student or group of students through a check-in procedure.

Fragmond
  • 3
  • 4
  • With this model a Group can only have one User and one Student. Is that correct? Or do you, as I suspect, want a group to be able to contain many users and many students? Perhaps expand on what you need to be able to show. Im also a little perplexed by the distinction of Users and Students. Can a Student have many Users? – Kent Chenery Nov 28 '17 at 00:11
  • Thanks, I tend to put the notations backwards. While I didn't fix the image hopefully the clarification will help. – Fragmond Nov 28 '17 at 00:16

1 Answers1

1

Based on your description you want to have a GroupMembers table that does all the many to many joins for you.

Your current schema only allows a Group to contain one User and one Student as you have made the GroupName the Primary Key. You really want to break the Group out to its own table in case the group name changes.

This can allow you to have:

  • A Group with many Users and many Students
  • Users can be members of many Groups
  • Students can be members of many Group
  • A User should not be duplicated in a Group
  • A Student should not be duplicated in a Group

Thats achieved with a schema like this:

enter image description here

Because you only ever want one combination of a given group, user and student you can make the foreign key columns the primary key too.

This all follows Third Normal Form (at least).

Kent Chenery
  • 541
  • 2
  • 7
  • That is kind of what I was thinking, but as I'm still relatively inexperienced with relational databases I wanted to get clarification. Your help is much appreciated. – Fragmond Nov 28 '17 at 01:25
  • If the above doesnt answer your question; please feel free to ask via a comment or update your original question and I'll amend. – Kent Chenery Nov 28 '17 at 01:35