Dynamic Tables?

Question

Hello, 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.

Answer 2

Decompose the table into three different tables. One holds the courses, the second holds the homeworks, and the third connects them and stores the result.

Course:

CourseID CourseName
1        Foo

Homework:

HomeworkID HomeworkName HomeworkDescription
HW1        Bar          ...

Result:

CourseID HomeworkID Result
1        HW1        100

Answer 3

You should have a table of courses:

courses
id   name
1    First Period
2    Second Period
3    Third Period

And a table of assignments with a foreign key pointing to the course:

assignments
id   course_id   name
1    1           Test 1
2    1           Homework 1
3    1           Homework 2
...

Then a table of grades pertaining to each assignment:

grades
id   assignment_id  value
1    3              97
...