Design question about student assessment data

Question

I am designing a relational database in Filemaker Pro that will be tracking assessment data for students in after school tutoring programs. Depending on the student's grade level and skill set, each student may have a number of different assessments. Thus, there will be a many-to-many relationship between students and assessments.

What I am trying to determine is whether I should make a single assessment table (example header row: foreign key student ID, foreign key assessment ID, student score, date) or whether I should have a separate table for each assessment (example header row: foreign key student ID, student score, date).

The reason I might want a separate table for each assessment is that not every assessment will have the same descriptions, so I may not be able to create a single assessment_info table that encompasses all that info. For instance, some assessments will have a total possible score, whereas others will only have a pass/fail indicator.

Any advice for how to think about this design decision would be appreciated.

Answer 1

The first thing you want to do to keep your thinking straight is to choose some clear terminology. You are using the word "assessment" in two different contexts: (i) a Test and (ii) the results for a student taking the test. I understand that "test" may not be the best term, since you might be assessing based on lots of different kinds of work. The important thing is that you need to clearly differentiate between a measuring instrument and the results of an individual student against that measuring instrument.

Keeping these concepts separate will help you to keep your thinking straight when designing your database.

I'd recommend that all of your RESULTS be kept in a single table. That will make things like report cards easier to handle. Whether the result is a percentage, a letter grade or a pass/fail, you probably want to have a common representation (percentage is a good lowest common denominator) and a weight to help you aggregate marks across assessments.