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

Marc B · Accepted Answer · 2012-01-23 19:40:29Z

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.

answered Jan 23, 2012 at 19:40

Marc B

362k44 gold badges433 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Josh · Accepted Answer · 2012-01-23 19:39:45Z

0

I would suggest creating a union table between users and courses. Using a comma separated field is pretty hacky for what you're doing here.

Users:

user_id | user_name | ...

Courses:

user_id | course_name | ...

User_Course_Union

union_id | user_id | course_id | ...

Then you can easily do queries based off of SELECT COUNT(union_id) AS count WHERE ... and get all the data you'll need.

answered Jan 23, 2012 at 19:39

Josh

12.6k12 gold badges77 silver badges118 bronze badges

1 Comment

Bernard Over a year ago

thanks for your response. but doesn't this need a additional step in the input mask? (new user > then select the course to the user)

Biotox · Accepted Answer · 2012-01-23 19:52:13Z

I would do three tables. userInformation, courseInformation and userCourseInformation. userCourseInformation would keep which user is taking which courses.

userInformation
user_id, user_name, etc..

courseInformation
course_id, course_name, etc..

userCourseInformation
user_course_id, user_id, course_id, etc..

All of the enrolled courses go on the userCourseInformation and reference the other two tables id as foreign keys. When you want to offer a new course, you add it to courseInformation. Add a new user, create a new userInformation. The majority of the UPDATE/DELETE/SELECT will happen on the userCourseInformation as students come and go. You could also just use a flag (boolean) to show is active or not. It means you can always go back and look at past data.

Jonathan M · Accepted Answer · 2012-01-23 20:11:18Z

0

When you have a many-to-many relationship (e.g. courses to students) you typically should utilize a table in-between them. That is, you should have your course table, your student table, and then your course-student table (or "enrollment" table). The enrollment table could look like this:

Id | Student | Course | Grade    |
1  | A       | 10     | Pass     |
2  | B       | 10     | Fail     |
3  | A       | 12     | Withdraw |

This shows student A in two courses (10 and 12), and two students (A and B) in course 10. A benefit of doing it this way is that you can keep data that is specific to the enrollment, such as grades or pass/fail status (as shown in the example).

edited Jan 23, 2012 at 20:11

answered Jan 23, 2012 at 19:41

Jonathan M

17.5k9 gold badges61 silver badges95 bronze badges

Collectives™ on Stack Overflow

Checkbox data in column?

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related