CODEWITHSUNDEEP
javamysql
Yy--
Yy--

Reputation: 23

only a column, How to design a relations for A one To Many B in mysql

1).it's one to many
2).it can used to query
this is my idea, maybe have another better?

select * from table where student_id in (7,1);

 column(teacher_id)     column(student_id)

   1               7
   1               9
   1               11
   2               1

//for example:select * fro table where categories contains '|9|' or categories contains '|1|';

 column(sport_id)     column(categories)

   1               |7|8|9|10|11|12|

   2               |1|9|

both two method,one is teacher with student(combination)

one is categories is a attribute in sport,

I think, attribute no need to use many column, right? thanks for answer.

Upvotes: 0

Views: 33

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521103

Your first table design is fine, and is typical for a one to many relationship. But, as for your the second table, that data is not normalized, so probably should not actually exist in your database. If you need to generate a view of your table which looks like the second example, then MySQL offers a handy aggregate function GROUP_CONCAT which can be used, e.g.

SELECT
    teacher_id,
    GROUP_CONCAT(student_id SEPARATOR '|') students
FROM yourTable
GROUP BY
    teacher_id;

The main reason why the second unnormalized version is bad is that it is difficult to query pipe separated data like that. Databases were designed to apply functions and logic over rows, not really over columns.

Upvotes: 2

Related Questions