Logic for handling volunteers assigned for requests for the managed tab user case

PROBLEM :

Logic for handling volunteers assigned for requests for the managed tab user case. The managed request tab needs information of all the requests assigned to the user/volunteer.

SOLUTION 1:

Addition of a column/flag in the requests table to indicate whether a request has one or more helpers. If there are helper volunteers along with the lead volunteer, then the helper volunteers will be added to the volunteers_assigned table.

Request Table Structure:

• Includes the leader volunteer ID.
• Contains a flag indicating the presence of helpers.

Query Logic:

• If the flag is "Yes", query the volunteers_assigned table to retrieve all helpers.
• If the flag is "No", skip querying the additional table.

To show data for managed requests for a volunteer, backend perform multiple queries:

• Query the Requests table to find requests managed by the leader.
• Query the volunteers_assigned table to get all request IDs where the person is a helper.
• Use the IN clause to get all request details in one query for helper requests.

This approach needs the backend to query the database 3 times. One to collect all the requests where the volunteer is a lead volunteer (from the request table) and another to collect the requests where the volunteer is a helper (from the volunteers_assigned table). Then to fetch the requests data from the request table.

SOLUTION 2:

Addition of a column/flag in the Requests table to indicate whether a request has one or more helpers.

Request Table Structure:

• Includes the leader volunteer ID.

Query Logic:

• Query the volunteers_assigned table to retrieve all requests for the volunteer.

To show data for managed requests for a volunteer, backend performs one query:

• Query the volunteers_assigned table to get all request IDs where the volunteer is assigned to the request.
• Use the IN clause to get all request details in one query for helper requests.

This approach needs the backend to query the database 2 times. One to collect the requests where the volunteer is assigned to a request (from the volunteers_assigned table). Then to fetch the requests data from the request table.

Thus, the 2nd approach is better than the 1st.