Why not to put multiple values into single MySQL field?

PHP
1

Hello,

Im working on a site that has users, and classes. Each user can be enrolled in one or more classes. Each class has a class code that is added to the users “classcode” field when they enroll in the class. Ive been looking for a way to add multiple values to one field and found that most people advise against it for semantic reasons.

Im wondering what the better practice would be for this situation. How would I relate a specific user to more than one class without doing it this way?
The only other option I thought of would be to dynamically add a field to the users table when a class is created but this will raise problems and be a strange workaround in my mind.

If anybody has any input on this, Id love to hear it, thanks!

2

  1. Do not put multiple vvalues in the same field.

  2. Do not add multiple fields to hold similar content.

Doing either of those means that your database is no longer properly normalized.

What you need to do is to add another table that has two fields - user and classcode. You can then delete the classcode field from the first table and use the second table to define anywhere between zero and a trilliion+ classcodes for each user.

3 

users
====
id | name
---------
23 | Bob

classes
======
id | class_name
---------------
1 | English
2 | French

user_class
========
user_ref | class_ref
------------------
23 | 1
23 | 2

Elaborating on @felgall; answer really, that is how your db tables could be structured to give you and idea.

To get the data out in a single query, so that Bob has classes in English and French, you’d need to read up on how to do table JOINs, and also bone up on the degrees of database normalization.

4

Except that the students probably all have a student number and I distinctly rememeber that the OP said each class has a class-code so you would use those meaningful fields instead of the id fields. You should only generate ids when there is no value to uniquely identify each row of a table or where the unique identifier is huge.

5

My example was only to pique the OPs interest a little further by showing what a referential table was. I bet there are no students called just Bob either.

6

Thanks guys! the referential table concept was the missing piece i needed to understanding how these database relationships worked.

7

Are you sure you understand the rest of how database normalization works? The piece you were missing is such an early part of the entire concept of how to create normalized relational databases that there are probably other parts of normalization that you also haven’t considered.

8