SQLITE - dealing with derived attributes-CodePudding

I am a CS student trying to build an app to store the grades of my dear colleagues. To do that I will be using SQLite to store the grades. In my database, there will be a table called Student (each one of my colleagues), a table Course (the courses we had), and a table Enrollment that stores the information about which course each one took.

I also want to have in the Student table two derived attributes average_grade and total_credits. As you might already imagine I will have to take this information out of the other two tables. The problem I am facing is: how should I do this?

I was thinking about using views but I sort of will be using this all the time and I don't think my app will perform well. Then I thought about materialized views but I discovered that SQLite doesn't support it, and now I am thinking about using TRIGGERS but I think I might be complicating this.

Is there any nice and clean way of doing this in SQLite? Have these values pre-computed, as a cache, and only change them when I change certain tables?

Note: I just want to freeze that I will rarely insert data into this database (only at the end of the semester or after the exam results come out).

CodePudding user response：

According to your description, you have a data model like this:

If you want to update average_grade and total_credits in student table when a new row is inserted into enrollment table, you can create a trigger to do so:

create trigger if not exists tr_enrollment after insert on enrollment
for each row
begin
    update student
       set average_grade = c.average_grade,
           total_credits = c.total_credits
      from (select e.student_id          as student_id,
                   avg(e.grade)          as average_grade,
                   sum(c.course_credits) as total_credits
              from enrollment e
              join course c
                on e.course_id = c.course_id
             where e.student_id = NEW.student_id 
             group by e.student_id) as c
     where student.student_id = c.student_id; 
end;

However, you may need to consider if UPDATE and DELETE operations may happen on enrollment table and then create similar triggers accordingly.