MySQL rating database structure

I'm trying to create a database that stores the students grade for each homework assignment I want to be able to sto开发者_JS百科re the grade and how many times that student got a certin grade for example a student got an A+ 30 times, for each student, as well as how many grades a student got. And how much a grade is worth in points for example an A is worth 4.3.

So I was wondering what is the best way to build my MySQL database what do I need to add and what do I need to drop and how can I store numbers like 4.3 for example.

My database so far

CREATE TABLE grades (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
grade INT UNSIGNED NOT NULL,
student_work_id INT UNSIGNED NOT NULL,
student_id INT UNSIGNED NOT NULL,
date_created DATETIME NOT NULL,
PRIMARY KEY (id)
);

CREATE TABLE work (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
student_id INT UNSIGNED NOT NULL,
title TEXT NOT NULL,
content LONGTEXT NOT NULL,
PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS student (
id int(8) NOT NULL auto_increment,
student varchar(20) NOT NULL,
PRIMARY KEY  (`id`)
)

example of output.

student |   grade   |   count

    1       A           10
    1       C           2
    1       F           4
    2       B           20
    2       B-          3
    2       C+          10


student |   grade   |   points

    1       A           4.3
    2       B+          3.3
    3       B-          2.7
    4       D+          1.3


student |   total grades

    1       90
    2       80
    3       100
    4       1

I think a student - grade - assignment schema would be ok. You should count the number of grades of a student with a query and not maintain that information in your database. At a conceptual level (not strict mysql), I would do something like this.

table student (
  id int (primary key)
  varchar name
  ...                           // other student attributes (e.g. phone number)
)

table grade (
  id int (primary key)
  name varchar                  // e.g. "A+", "B-", etc.
  float points                  // grade value in points
)

table assignment (
  id int (primary key)
  name varchar                  // assignment name
  student_id int (foreign key)  // student that completed the particular assignment
  grade_id int (foreign key)    // grade achieved in particular assignment
  ...                           // other assignment attributes (e.g. due date)
)

Many would suggest that you have a student table, a grade table and a table that relates the student and grade table. You could then do counts based on the table that relates the two with a procedure. Many believe that having a grade and a student in the same table is poor relational design.

Unless you want to use a view, which is really not necessary in this case, you wouldn't store the amount of times a student got a certain grade; rather, you would make a query that gave you that information for each student when you needed it. One example using your current schema, to tell you how many times a particular student having id st_id got each grade, would be like

SELECT COUNT(1) FROM grades WHERE student_id = st_id GROUP BY grade

This will look at all the rows where student st_id got each grade and count them up separately.

As far as storing a mapping from letter grade to the amount of points that its worth, you could do one of two things - either have a table mapping from letter grade to point amount, or just store the point amounts directly. Personally, I think the former is a little bit cleaner as it allows you to change point amounts if the scale changes (not that that should happen often).

Other than that, I think you're doing pretty good. The only thing I would say is that if there is always a one-to-one mapping between grade and work, I might make grade part of the work table rather than separate them. That is, only if there isn't information in the grade table that doesn't belong in the work table.

Looking at your tables it appears that there's some normalizing that you can do, or I might be misunderstanding the objective. I would assume that students and work items are only connected by a grade, which would make your grades essentially a cross-reference table. As a result, you wouldn't reference the student_id column within the work table. Off the cuff, I would change it such that the tables look more like this:

CREATE TABLE student (
student_id int(8) NOT NULL auto_increment,
student varchar(20) NOT NULL,
PRIMARY KEY  (student_id)
);

CREATE TABLE work (
work_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
title TEXT NOT NULL,
content LONGTEXT NOT NULL,
PRIMARY KEY (work_id)
);

CREATE TABLE grades (
grade_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
grade decimal(4,2) UNSIGNED NOT NULL,
work_id INT UNSIGNED NOT NULL,
student_id INT UNSIGNED NOT NULL,
date_created DATETIME NOT NULL,
PRIMARY KEY (id)
);

Note that I changed the grade column to a decimal (I'm a SQL Server guy, so MySQL might be different), but any type that can handle non-integer numbers works. With that design, you can then pull your queries as needed to get the types of output you need.

Josh

assuming -

1. there's more than one student

2. there's more than one assignment / piece of work to be graded against

3. more than one student can complete a specific set work/assignment
4. only one grade can be earned per assignment

I'd go with -

1. a table for the definition of each set piece of work / a specific assignment.

2. a table to define each student

3. a third table to record the result of each assignment per student, ie to store the grade per assignment.

eg -

CREATE TABLE work (
id            INT UNSIGNED NOT NULL AUTO_INCREMENT,
student_id    INT UNSIGNED NOT NULL,
assignment_id int unsigned not null,
grade         DECIMAL(1,1) 
gradedesc     VARCHAR(1) 
PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS student (
id       int(8) NOT NULL auto_increment,
student  varchar(20) NOT NULL
PRIMARY KEY  (`id`)
)


Create table assignment (
id         int(8) not null auto_increment,
title      TEXT NOT NULL,
content    LONGTEXT NOT NULL
primary key(id) 

Then, create some views to display the output in the format you want, eg -

Create view graded_students as 
(select  s.student, 
         w.gradedesc as grade, 
         w.grade as points, 
         w.title
from work w, 
     student s
where w.student_id = s.id 
and   w.grade is not null )

Create view aggregate_grades as 
(select  s.student, 
         sum(w.grade) as "total grades", 
from work w, 
     student s
where  w.student_id = s.id 
and    w.grade is not null 
group by s.student) 

create view grades_by_assignment as 
(select s.student, 
        a.title, 
        w.gradedesc as grade
from student s, 
     work w, 
     assignment a
where a.id = w.assignment_id
and   s.id = w.student_id )

I'm more of an oracle and sql server person, so this may not be 100% mysql compliant (and I haven't tested it) but something like that would do the trick I think.

If you want to normalize the data even further you could break out the grade description/value to another table too, or just display the grade description in your application based on the stored value (eg grade value 4.5 always displays as "A", etc)