Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
This may help: dba.stackexchange.com/questions/16002/… –  Michael Durrant Apr 21 '12 at 23:55
add comment

1 Answer

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.