0
\$\begingroup\$

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. enter image description here

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`
\$\endgroup\$
1
  • \$\begingroup\$ In addition to your diagram can you post the actual DDL? \$\endgroup\$
    – Reinderien
    Apr 15, 2021 at 15:01

1 Answer 1

1
\$\begingroup\$

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: User to Course relationship

  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: Lookup table structure

  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

Notes in response to comments:

  • If you want to share a course between users, you will need to change your schema to be similar to mine. If, as suggested in the comments, only one user will ever use a course, then you're probably okay with the schema your using. I just don't generally think of a user as an attribute of a course. It's a little ambiguous: is that user a teacher, student, or what have you.
  • One thing to think about for lookup tables is that if you're not using an id, then the value is duplicated. If you ever update the lookup table, you have to change all values in the other table as well.
  • I do definitely think yearGroup is a misnomer. Where I come from, courses are offered by semester or some other frequency besides yearly.
\$\endgroup\$
4
  • \$\begingroup\$ Thanks, first of all, for taking the time to review! Essentially, the idea is, the user will be able to re-create the course that they're on so they can predict their own grade and keep track of their progression. A user can create one or many courses, a course can have one or many year groups, a year group can have one or many units, and finally a unit can have one or many assignments. In regards to the lookup table, the reason I didn't include a primary key is because it saved me having to write a join and also it ensured that the lookup value is unique. And thanks very much for saving! \$\endgroup\$ Apr 15, 2021 at 16:46
  • \$\begingroup\$ and thanks very much for saying* \$\endgroup\$ Apr 15, 2021 at 16:58
  • \$\begingroup\$ You're welcome :) I would also suggest you add that contextual info into your question for others to be able to see. \$\endgroup\$
    – Airistotal
    Apr 15, 2021 at 17:36
  • \$\begingroup\$ You have made a solid point about if a value in the lookup table was to change it would affect all places I've used it. I shall definitely amend this! Thinking about it, you're right about the yearGroup table! Again, greatly appreciate your feedback! \$\endgroup\$ Apr 15, 2021 at 19:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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