Calculated database fields

Data Service
1

What is the most efficient way to update a database field based on a calculation, which could potentially involve logic?

Imagine having a Student table with a field called coursesAvailable, which is a int field that stores how many courses a student can take at a given time based on criteria/logic/aggregation that considers data from other tables.

For example, student Marc can have 27 courses available to choose from at registration time. The number 27 is arrived at based on what various departments offer, profile considerations, etc.

Is there a backend feature available to update the coursesAvailable field automatically? I need to update this column for all students in an efficient manner, and the number of students could be in the many hundreds, thousands, or even millions.

2

What is the event that triggers the update logic? Is it a time-based frequency (once an hour/day/week) or something else that happens in the system?

3

The trigger is flexible and is up to me as the developer to decide on. It could be time-based or based on, for example, a new course being created in the database. Ultimately the goal is for the coursesAvailable column in the Student table to always be up to date, or as close as “always” as possible. If we go with a time-based trigger, it would need to run as often as possible. Again, I could have a huge amount of students, so that needs to be considered as well (considering script timeouts, etc.).

4

Avaialble options are:

  • If the input data to calculate that field is available in the same table, you could use “Generated columns”.
  • Rather than pre-calculating the value to store in the DB, do it whenever you need to retrieve it to display in the UI
  • Create a timer that runs as frequently as you need to. The timer would not need to retrieve all records to do the calculations for all students. Instead, run the timer every 5 minutes and load only the students that don’t have the value calculated (and the ones for which the value is invalidated).

Hope this helps.
Mark

5

Thanks Mark. The third option is best for my case. The input data is in other tables and given my UI requirements, the second option would lead to performance issues.