evilxbyte evilxbyte - 1 year ago 77
MySQL Question

Information separating from mysql

I'm making program for teachers with Java and mysql , so I've many tables in mysql such as students table which contains student name and number and ID .. etc , also another table that called groups , It contains group name , place and students .. etc.
So , the problem is , in Groups table I'll add IDs of students in one row just like the image bellow beside the name of group , how can I take this IDs from mysql in this form and separate them automatically in program by java to search in the students table about the information of them .

image : http://i.stack.imgur.com/NFQ6t.jpg

try {
Connection con = (Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/teacherassistant?allowMultiQueries=true","root","1234");
Statement st = (Statement) con.createStatement();
String query = "select * from Groups;";
ResultSet rs = st.executeQuery(query);

String name = rs.getString("Name");
String StudentIDs= rs.getString("Students");
String Center = rs.getString("Center");


} catch (Exception e) {


Don't do that.

The relationship between groups and students is clearly a many-to-many relationship. Instead make a new table that contains this relationship. Something like;

(student_id, group_id)

There is a related question and a very good answer about m:n relations here