# Entity Relationship Diagram for Grade Calculator

I was wondering if anyone would be able to review my Grade Calculator for a course ERD? I'm looking for ways to improve the current design (if any improvements need to be made that is) and if anyone could review the current MySQL query which is there. In order for this to be reviewed I was told I needed an SQL query to accompany it.

SELECT
yearGroup.id AS year_group_id,
yearGroup.year AS year_group,
CONCAT(
TRUNCATE
(
COALESCE(
SUM(
assignment.percentage_achieved *(
assignment.percentage_weighting / 100
)
),
0
) /(
SELECT
COUNT(*)
FROM
unit
WHERE
unit.year_group_id = yearGroup.id
),
2
),
'%'
) AS grade
FROM
assignment
RIGHT JOIN unit ON unit.id = assignment.unit_id
RIGHT JOIN yearGroup ON yearGroup.id = unit.year_group_id
WHERE
yearGroup.course_id = 1
GROUP BY
yearGroup.id

• In addition to your diagram can you post the actual DDL? – Reinderien Apr 15 at 15:01

I've got a few thoughts:

1. The user table is related to the course table in a funny way. At first when I was looking at the diagram I assumed users were students, but after staring for a while, that seemed less and less likely. I'm now under the impression that the user is the owner of the course, and I think that having the user_id in the course table is a little funny. I would use a separate mapping table like so:

2. The lookup tables seem fairly useless as you are using the actual value in the base tables instead of a lookup id. Changing it to look like this will add usefulness:

3. Your naming is a little strange. I would change the course table to be named courseInfo, and change yearGroup to be named course. This might be a little nitpicky as this is a generally well done diagram. :P