Complicated MySQL Stored Proceedure

Question

I have a semi-complicated stored proceedure that I need to create. I'm not exactly a DBA here but no one else in my company is better than I am. The basic idea is I have a group of tasks. The user completes these tasks and gets a flag for each task completed. To be considered "complete" the user has to have all of a group of given tasks finished. The lynch pin is that some of those tasks my be wrappers for other groups of tasks.

For example, we have the following possible tasks:

Task 1
Task 2 
Task 3
Task 4 -> (1, 2, 3)
Task 5 -> (1, 2)
Task 6
Task 7 -> (5, 6)

Once a user has completed tasks 1 and 2, they have implicitly completed task 5. Once they finish task 3, they'll have implicitly completed task 4.

If another user finish tasks 1, 2 and 6, they will have implicitly finished 1, 2, 5, 6, 7.

In reverse, if an assignment required only Task 7, it would by extension be requiring tasks 6 and 5, which would required 1 and 2.

I have five basic tables: userCompletedTask, assignmentRequiredTask, compoundTask, userTaskAssignment and task. I omit the user and assignment table as it's superfluous.

userCompletedTask:
userID (Foreign Key on user)
taskID (Foreign Key on task)

usertTaskAssignment:
userID (Foreign key on user)
assignmentID (Foreign key on assignment)

assignmentRequiredTask:
assignmentID (Foreign key on assignment)
taskID (Foreign key on task)

task:
taskID (primary key)
compound (boolean flag. If 1, it is compound)

compoundTask:
parentID (foreign key on task)
childID (foreign key on task)

A user gets assigned a userTaskAssignment which requires task 4 to be completed. I want to build a stored proceedure that would check the userCompletedTasks against the assignmentRequiredTasks, checking if there is appropriate compoundTasks.

The pseudo code would like this:

collection tasksCompleted = user->getTasksCompleted
collection tasksRequired = new collection

foreach task in assignment->getRequiredTasks 
   if(task.isCompound) 
      tasksRequired->addAll(getCompountTasks(task))
   else 
      tasksRequired->add(task)

if tasksCompleted->containsAll(tasksRequired)
   return true
else 
   return false

I just don't know MySQL/SQL's internals well enough to translate this into a stored proceedure. Last resort is I pull the code into the application but this would really fit at the data level. Any help would be greatly appreciated.

EDIT

As was pointed out below, compoundTask entries could themselves be compound tasks. Thus you would need a recursive drill to get a list of all the non-compound tasks that would need to be finished. I expanded the example above to exemplify this.

Answer 1

Really, your compoundTask table is duplicating your task table. If you simply add a nullable parent_id column to the task table, you can achieve the same results in one table.

Here it goes, this is untested and maybe not even valid MySQL, but it should get you started:

SELECT DISTINCT taskID FROM task AS t LEFT JOIN compoundTask AS ct ON ct.taskID = t.taskID
INNER JOIN userCompletedTask AS uct ON uct.taskID = t.taskID
INNER JOIN userCompletedTask AS uctCompound ON uctCompound.taskID = ct.taskID
WHERE uct.userID = @user AND uctCompound.userID = @user

Whatever taskIDs are returned from this are completed for the specified @user.

It's been a long time since I've done MySQL - so this may not even work. Also, if you could consolidate the compoundTask and task tables the 2nd INNER JOIN would be unnecessary.

Answer 2

I am new to mysql, but I am not new to T-SQL store procedures. Here is what I think should be the stored procedure. I think it is a mix of T-SQL and mysql.... so please eliminate the syntax worries appropriately

CREATE PROCEDURE proc (@userId BIGINT)
AS
BEGIN

/*
   Consider this table the same as
   collection tasksCompleted = user->getTasksCompleted
*/
CREATE TABLE #TasksCompleted
(
   taskID BIGINT
);

INSERT INTO #TaskCompleted
   (SELECT
    task.taskId
    FROM
    userCompletedTask natural join
    task
    WHERE
    userCompletedTask.userId = @userID AND
    task.completed = 1);


/*
   Same as collection tasksRequired = new collection
*/
CREATE TABLE #RequiredTasks
(
   taskID BIGINT
);

CREATE TABLE #RetrivedRequiredTasks
(
   taskID BIGINT,
   compound BIT
);

INSERT INTO #RetrivedRequiredTasks
   (SELECT
    task.taskId,
    task.compound
    FROM
    usertTaskAssignment NATURAL JOIN
    assignmentRequiredTask NATURAL JOIN
    task
    WHERE
    userCompletedTask.userId = @userID);

INSERT INTO #RequiredTasks
   (SELECT taskID FROM #RetrivedRequiredTasks WHERE compound = 0);

INSERT INTO #RequiredTasks
   (SELECT
    compoundTask.childID
    FROM
    #RetrivedRequiredTasks INNER JOIN
    compoundTask on
    (
        #RetrivedRequiredTasks.compound = true  AND
        #RetrivedRequiredTasks.taskId = compoundTask.parentID
    ));

DECLARE @count INT

SELECT @count = Count(*)
FROM #RequiredTasks
WHERE taskId NOT IN (
    SELECT taskID FROM #TaskCompleted);

IF @count = 0 THEN
   SELECT 1 /* All required tasks completed */
ELSE
   SELECT 0 /* vice-versa */

END

Answer 3

If you will only ever have one level of tasks then a better model might be to have a task table and a subtask table. For non-compound tasks they would simply have a single subtask under them instead of multiples. You can do this in your own model by just adding rows for all non-compound tasks where the parentid = childid.

All of that aside, the following code should give you a list of all uncompleted tasks that are required of the user:

SELECT
    COALESCE(CT.child_id, T.task_id)
FROM
    User_Task_Assignments UTA
INNER JOIN Assignment_Required_Tasks ART ON
    ART.assignment_id = UTA.assignment_id
INNER JOIN Tasks T ON
    T.task_id = ART.task_id
LEFT OUTER JOIN Compound_Tasks CT ON
    CT.parent_task_id = T.task_id AND
    T.compound = 1
LEFT OUTER JOIN User_Completed_Tasks UCT ON
    UCT.user_id = @user_id AND
    UCT.task_id = COALESCE(CT.child_id, T.task_id)
WHERE
    UTA.user_id = @user_id AND
    UCT.user_id IS NULL