Take the following statement for example:
'Get results for ALL Active students of a Course'.
Naturally, this query has 2 parts,
- Get all active students of the course.
- Get results of those active students.
The end result can be achieved with a single query using JOIN. Now, in some situations, some students may have their result missing (the teacher have not yet input mark). In such case, their result will be shown as 0 (or as , it doesn't matter). In this situation, the result of the 2nd query won't contain All the active students. But, it can be handled using LEFT JOIN to always show all students.
Now, the conditions to determine Active Students requires JOINING 2/3 tables now and may change in future. Similarly, logic for calculating mark may also change.
So, there are 2 ways to solve this. I am currently using Single Query approach as it uses a Single DB query to get all necessary data. Also, DBs are best at handling and generating data.
On the other hand, writing separate SQL queries to separately retrieve Students and Results and combining them on service logic would make maintaining the code very easy. But this will probably be slower, especially for large result set.
Another thing to consider is that there can be more queries in the form of
"Get (some data) for All Active students of a Course". So, a change in requirements will affect ALL the queries.
So, considering execution speed and ease of coding what would be the better approach. I like the break and combine approach but unsure about the speed overhead. What would you do in this approach?
Platform: NodeJs, DB: MySQL