How to get rid of multiple columns in a database?

Question

I'm creating an Access DB for use in an C# application at school. I've not had much experience working with DB's so if this sounds stupid just ignore it. I want the user to be able to select all the classes that a certain student has had in our IT department. We have about 30 in all and the maximum that a person can take in 4 years of high school is 15. Right now my DB has 15 different columns for each class that a user could have. How can I compress this down to one column (if there is a way)?

Answer 1

Excellent question Lucas, and this delves into the act of database normalization.

The fact that you recognized why having multiple columns to represent classes is bad already shows that you have great potential.

What if we wanted to add a new class? Now we have to add a whole new column. There is little flexibility for this.

So what can be done?

We create THREE tables.

One table is for students:

Student
   |-------------------------|
   | StudentID | Student_Name|
   |-------------------------|
   |     1     |     John    |
   |     2     |     Sally   | 
   |     3     |     Stan    | 
   ---------------------------

One table is for Classes:

Class
   ------------------------
   | ClassID  | Class_Name|
   ------------------------
   |    1     |   Math    |
   |    2     |   Physics |
   ------------------------

And finally, one table holds the relationship between Students and Classes:

Student_Class
   -----------------------
   | StudentID | ClassID |
   -----------------------

If we wanted to enroll John into Physics, we would insert a row into the Student_Class table.

  INSERT INTO Student_Class (StudentID, ClassID) VALUES (1, 2);

Now, we have a record saying that Student #1 (John) is attending Class #2 (Physics). Lets make Sally attend Math, and Stan attend Physics and Math.

  INSERT INTO Student_Class (StudentID, ClassID) VALUES (2, 1);
  INSERT INTO Student_Class (StudentID, ClassID) VALUES (3, 1);
  INSERT INTO Student_Class (StudentID, ClassID) VALUES (3, 2);

To pull that data back in a readable fashion, we join the three tables together:

  SELECT Student.Student_Name, 
         Class.Class_Name 
  FROM Student, 
       Class, 
       Student_Class 
  WHERE Student.StudentID = Student_Class.StudentID 
       AND Class.ClassID = Student_Class.ClassID;

This would give us a result set like this:

  ------------------------------
  | Student_Name  | Class_Name |
  ------------------------------
  |    John       |  Physics   |
  |    Sally      |   Math     |
  |    Stan       |  Physics   |
  |    Stan       |   Math     |
  ------------------------------

And that is how database normalization works in a nutshell.