Dynamic Tables?

Question

I have a database that has different grades per course (i.e. three homeworks for Course 1, two homeworks for Course 2, ... ,Course N with M homeworks). How should I handle this as far as database design goes?

CourseID HW1  HW2 HW3
    1    100  99  100
    2    100  75  NULL

EDIT I guess I need to rephrase my question. As of right now, I have two tables, Course and Homework. Homework points to Course through a foreign key. My question is how do I know how many homeworks will be available for each class?

Answer 1

No, this is not a good design. It's an antipattern that I called Metadata Tribbles. You have to keep adding new columns for each homework, and they propagate out of control.

It's an example of repeating groups, which violates the First Normal Form of relational database design.

Instead, you should create one table for Courses, and another table for Homeworks. Each row in Homeworks references a parent row in Courses.

My question is how do I know how many homeworks will be available for each class?

You'd add rows for each homework, then you can count them as follows:

SELECT CourseId, COUNT(*) AS Num_HW_Per_Course
FROM Homeworks
GROUP BY CourseId

Of course this only counts the homeworks after you have populated the table with rows. So you (or the course designers) need to do that.