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)?

Hayashi

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.

Jessen

Excellent response. In additin to the (valuable, correct) info given I would add that you can store additional data about the classes taken. If you DB is of use to the Org it will be enhanced in later years. This design allows for that. Year class taken, tutor, result, etc can be added. – Zoltai

Thanks for the compliment but honestly I just couldn't stand scrolling horiszontally through 15 classes. Your answer really helped and now I've started reading about normilization. I think with the help of your design I'll be able to make a decent app for my teacher (she's actually going to use it). – Hayashi

Answer 2

It sounds like you need to think about normalizing your database schema.

There is a many-to-many relationship between students and classes such that many students can take many classes and many classes can be taken by many students. The most common approach to handling this scenario is to use a junction table.

Something like this

Student Table
-------------
id
first_name
last_name
dob

Class Table
-----------
id
class_name
academic_year

Student_Class Table
-------------------
student_id
class_id
year_taken

Then your queries would join on the tables, for example,

SELECT
    s.last_name + ', ' + s.first_name AS student_name,
    c.class_name,
    sc.year_taken
FROM
    student s
INNER JOIN
    student_class sc
ON
    s.id = sc.student_id
INNER JOIN
    class c
ON
    sc.class_id = class.id
ORDER BY
    s.last_name, sc.year_taken

One word of advice that I would mention is that Access requires you to use parentheses when joining more than table in a query, I believe this is because it requires you to specify an order in which to join them. Personally, I find this awkward, particularly when I am used to writing a lot of SQL without designers. Within Access, I would recommend using the designer to join tables, then modify the generated SQL for your purposes.

Chalcopyrite

Answer 3

So you have 15 columns (e.g. class1, class2, class3 ... class15)?

Looks like you have a classic many-to-many relationship. You should create a new table to relate students and classes.

student { StudentID, StudentName ... }
classes { ClassID, ClassName ... }
student_classes { StudentID, ClassID }

If you are tracking classes on a year-by-year basis, you could add a year column to the relationship as well:

student_classes { StudentID, Year, ClassID }

Aircondition

Answer 4

This is a normalisiation issue. In effect you are asking the wrong question. In stead ask yourself the question how can you store 0 or more classes_taken? What other details do you need to store about each class taken? E.g. just the class taken, or data taken, result, etc?

For example consider somethin like the following

table Student
 id int
 name varchar(25)
 ...

table classes
 id int
 name varchar(25)
 ...

table clases_taken
 student_id int (foreign key to student.id)
 class_id int (foreign key to class.id)
 data_started datatime
 result varchar(5)
 tutor_id int (foreign key to tutor.id)
 ...

Zoltai

Answer 5

You should never have columns like class1, class2, class3, class4 etc in a database table. What you should have is a related table. Your stucture would be something like:

Student Table with the following columns
StudentID
StudentLastName
StudentFirstName 
(and so forth for all the data to describe a student)

Then

Course table with the following columns
CourseId
CourseName

Then

StudentCourse Table with the following columns
StudentId
CourseID
CourseDate

Now to find out what courses the person took you join these tables together. Something like:

Select StudentID,StudentLastName,StudentFirstName, CourseName, CourseDate
from Student 
join StudentCourse on student. studentid = StudentCourse.StudentID
join Course on Course.courseID = StudentCourse.CourseID 

Please read this link to start learning database fundamentals: http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx

Shy

I aqree...throw the flag if you ever have more than one column pointing to the same FK and that column has a number following the name. – Sholley

Answer 6

How about no class columns in the student table. Setup a new table with student id and class id columns. Each row represents a class the student took. Maybe add more columns such as: the year/semester, grade, etc.

Sholley

Can you explain this a little bit more, I don't exactly follow. – Hayashi