Best Architecture for User Groups in MySQL?

1
votes

Overview:

I am trying to create a user group system for an English teaching website.

Each user group has one teacher account assigned to it and can have up to five student accounts.

To do this we have created three tables: 'teachers', 'students' and 'groups'.

I also have a number of pages to create/view/assign-to groups, as you can see in the screen shot below:

enter image description here

Here is the structure of our tables and how they interact with one-another to make sure that students and teachers are properly assigned:

enter image description here

1 - So first we create a group (+ New Group page) which adds it to the group table, the group has a name, unique ID and unique teacher ID which is taken from the Teacher table.

2 - We then add up to five students to the group (Assign Students page). The student is considered "in the group" because on the student table the group name and id is added to the "group_name" and "group_id" columns respectively.

Question:

1 - Is my thinking correct on the database architecture and general structure here or are there some obvious things that I am missing?

2 - Because a single teacher can be assigned to many groups I need to be able to see all the groups that a single teacher is assigned to, should I add all the groups into one table "td" cell by concatenating them with a breakspace or should I have a separate row for each group... the picture below should explain it better...

enter image description here

Any wisdom on the matter would be appreciated :-) .

2
mysqldatabase-design

2 Answers

1
votes

1) Mostly ... see number 2.

2) No do not concatenate the ids into a single column :( . This is a many to many relationship which can easily be solved with a "man in the middle table". Basically you created a table TeacherGroup with columns TeacherGroupID, TeacherID, GroupID now you can add the same teacher to multiple groups.

Then all it takes to answer questions about teachers and groups are simple joins from the Teacher or Group tables on the TeacherGroup table. This way you can answer questions likes what group(s) is this teacher or all of these teachers in. As well as which teachers are in this or these group(s).

1
votes

1.) Seems correct based on your requirements.

You have a one-to-many relationship with teachers -> groups and then another one-to-many relationship with groups -> students

2.) I don't know what you're asking here, that'd be up to you on how you want to display this data. It seems you know how to fetch it...