How to optimize The Table inserted A record that cause Other Tables which directly count( or recurse count) The Tables record nums update in Java?

Question

Here is a model, We have

Academy--Major--Class--Student

A student only belong to A class

A class only belong to A major

A major only belong to A academy

class Academy {
    String name;
    Integer personCount;
}

class Major {
    String name;
    Integer academyId;
    Integer personCount;
}

class _Class {
    String name;
    Integer majorId;
    Integer personCount;
}

class Student {
    Long id;
    String name;
    Integer academyId;
    Integer majorId;
    Integer _classId;
}

so, for each entity , there is a table map to it now, we insert a record to Table student

when the record has saved, the Table class has to update its record (the student belong), set person_count = person_count + 1

after it has done, the Table major also has to update its record (the updated record belong), set person_count = person_count + 1

also, The table academy still has to do update like major and class

is there a better sloution to reduce the database write times? or a better table structure?

I'm still think of it, it do pressed the database of write

the limit of student records will below 100,000.

but in a specific time it will do high frequent inserts

I expected a fast response solution

Answer 1

Plan A: Use TRIGGER ... ON INSERT ...

Plan B: Explicitly write UPDATEs to bump the counters.

Plan C: Encapsulate Plan B into Stored Procedures.

Plan D: Don't keep the counts in the tables, but recompute them when needed. (This is the purist way -- "thou shalt not have redundant info".) For a thousand students, I doubt if there is much "pressure". For a million students, performance is likely to be poor. In that case, what you have (tables with personCount) is essentially Summary Tables