Mysql – Problem structuring database for an assessment rubric app

database-designMySQL

Hello everyone, I'm really troubled about how to design a database and I could use some help. I've done my research and found nothing about it.

I'm trying to build a simple app for my class that creates academic assessment rubrics so they are available to use them on my students and finally get grades.

I thought of using mysql for my database. The student part is done. But I can not think of a way to make the rubrics thing work.

I need to be able to create rubrics and inside that rubric create criterias, sub-criterias, sub-sub-criterias, levels and points. Here's and example of a rubric.

enter image description here

Could anyone give me some suggestion about how to model this database or any idea about it? Is a relational database a good approach for this problem? I'm kinda burnt with this and would appreciate
any recommendation.

Sorry for my English, not my native.
Regards.

Best Answer

So you'll have students with an primary id student_id. Assume courses have a primary id course_id.

So each course will have its own assessment as per the first column in the rubric.

 create table courses_info (
   course_id int unsigned not null,
   assessment_id int unsigned not null,
   assessment varchar(80) not null,
   primary key (course_id, assessment_id)
 )

Student assessments take student_id, course_id, assessment_id and store an assessment_result (assumed to be uniform over the entire institution):

 create table student_assessment (
   course_id int unsigned not null,
   assessment_id int unsigned not null,
   student_id int unsigned not null,
   assessment_result enum('Emergent',..., 'Excellent'),
   primary key (course_id, assessment_id, student_id)
 )

The rubric is the mapping between an assessment_id for each course_id with the learning_outcome being the element in the lookup table:

 create table rubric (
   course_id int unsigned not null,
   assessment_id int unsigned not null,
   assessment_result enum('Emergent',..., 'Excellent'),
   learning_outcomes text,
   primary key (course_id, assessment_id, assessment_result)
 )

To get the results you query and JOIN on the required columns: