Checkbox data in column?

Question

a training course database should get a update. right now every new user (editable by client's client) is stored into a given training course (editable by the client). the users are counted, so that there are never more than f.a. 12 users for each training course.

okay that was the old concept.

for the new one, every new user could be subscribed to more courses than one with checkboxes. the difficulty for me is now, that i'm having troubles to deal with the array concept in the SELECT/DELETE/UPDATE-statements in the given column (sID which is the training id)

Table training courses:

id | from_date             | til_date |          | ...   
---+-----------------------+---------------------+---------
2  |  2012-04-16 08:30:00  | 2012-04-16 08:30:00 | ...

Table training users:

id | sID | name     | ...                          
---+-----+----------+---------------------------------------
1  |  2  | User A   | ...
2  |  6  | User B   | ...

i liked the concept about writing the different courses in one column (2,4,7,9) seperated by ",". but than i will have the problem that i can't do a COUNT on that column any more. also the safed course data is only outputting the checked courses to the checkboxes. but the client's client have to update the course members manually. so i can't "hardcode" every course to a checkbox.

does anybody have an idea how to do this properly?

thanks and greetings, bernard

Answer 1

You'd need at least three tables.

courses: (id, name, etc....)
users: (id, name, etc....)
user_courses: (user_id, course_id)

Adding/removing a user from a course would involve updates on the user_courses table only. New user: add a record, old user: delete a record. Each user/course pairing gets ONE record to themselves in user_courses, and you can still do your counts quite easily.

Embedding comma-separated values into a field negates the entire purpose of a relational data. You lose the ability to relate data to each other, because your nice clean user<->course mappings are in this big blob of numbers/commas that the DB cannot interpret for you.