Sophisticated MySQL DBMS

Question

I have a semi-arid stored time that I need to create. I'm not quite the database administrator here, but no one in my company is better than me. The basic idea: I have a task group. The user performs these tasks and receives a flag for each completed task. To be considered "complete", the user must complete the entire group of defined tasks. Pin lynch is that some of these tasks are 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 the user completed tasks 1 and 2, they implicitly completed task 5. Once they complete task 3, they will have implicitly completed task 4.

If another user completes tasks 1, 2, and 6, they will be implicitly completed 1, 2, 5, 6, 7.

In the opposite case, if the task required only task 7, he would need an extension for tasks 6 and 5, which would require 1 and 2.

I have five main tables: userCompletedTask, assignRequiredTask, complexTask, userTaskAssignment and task. I omit the user and destination table as unnecessary.

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)

The user is assigned userTaskAssignment, for which task 4 is required. I want to create a stored device that will check userCompletedTasks for assignRequiredTasks, checking for the presence of the corresponding composite tasks.

Pseudocode would like to:

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 the internal components of MySQL / SQL well enough to translate this into a saved one. The last thing I pull the code into the application, but it really matches the data level. Any help would be greatly appreciated.

EDIT

, Task . , , , . , .

Answer 1

, compoundTask task. parent_id , .

, , , MySQL, :

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

, taskID , @user.

, MySQL, . , compoundTask task, INNER JOIN .

Answer 2

mysql, T-SQL. . , T-SQL mysql.... , ,

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 */

Answer 3

, . - . , , parentid = childid.

, , :

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