Group into Teams

[JimboUK]

Hi guys, really enjoying SQL here on my course using Java, servlets and JSP. I have a table 'students' with the following:

 

'studentID', 'forename', 'surname', 'courseCode', 'dob'.

 

I need to be able to group these students into teams. Could you help me out with the logic behind it?

 

Do I have to create a new table named 'teams', create a new column in both students AND teams called 'teamID', linking as FOREIGN KEY, and then join them together?

 

I need to be able to create a new team using html form / jsp / servlet (no problem), but then how would I add students from the 'students' table, or remove them. What am I looking at doing?

[fenway]

Do I have to create a new table named 'teams', create a new column in both students AND teams called 'teamID', linking as FOREIGN KEY, and then join them together?

Yes, that's the proper procedure.

[JimboUK]

Thanks for the quick reply!

 

How about adding to and from the team? Would that be changing the teamID to null or something?

[fenway]

Adding what?

[JimboUK]

Adding a student to and from a team, via a html form, then listing teams. These students also need to  be able to be removed from the team they are in, so that upon next viewing the list of teams with the students in them, the recently removed student is no longer in that team.

[JimboUK]

Also, I need to only allow a maximum of 4 people on the team at once.

[fenway]

Adding a student to and from a team, via a html form, then listing teams. These students also need to  be able to be removed from the team they are in, so that upon next viewing the list of teams with the students in them, the recently removed student is no longer in that team.

That's as easy and adding/remove the appropriate rows from the lookup table.

 

Also, I need to only allow a maximum of 4 people on the team at once.

That's easier to handle if your application.