Ben Ben - 3 months ago 9
MySQL Question

mySQL : How to code query

I have 2 tables

clients
and
grades
where
clients
has a
grade
column with indices from 1 to 10. Those indices correspond to the ID's in the
grades
table (of course). Let's say a client has the indices 1,5,8 in the
clients.grade
column than I want to fetch * from those rows in the
grades
table.

My first approach was to use a
grade
field of type char(20) in the
client
table and fill it with indices separated by commas like 1,5,8 or others numbers. Then reading those values from one client from the
client
table I explode (PHP) the result and getting an array with 3 (or more) single values. Then in a following query I select all those rows in the
grade
table .

Now one question is wether to use a char field or an enumeration field and the other (general) question is how to make a simple, clever query for the entire task.

I did not code it yet. The
grades
table will have max. 10 rows, where as the
client
table has around 2.000 rows growing up to max. 5.000 rows. I hope I could describe my approach understandable.

Any suggestions are welcome

Answer

Split the grade column into a new table (client_grade?) containing clientId and grade, and have multiple records for each client. The grades for a client can then be retrieved using a JOIN from the clients to the clients_grade table on the client field

Each field in a table should hold a single piece of information and comma-separating values will cause you extra processing. Using a separate table will also aid you if you want to find out who got a particular grade etc