I have been doing a great deal of searching, but weren't capable of finding many assets around the subject. Let me store arranging data as if you would get in a Gantt chart. So an example of storing the information may be:

Task Id | Name    | Duration
1         Task A    1
2         Task B    3
3         Task C    2

Task Id | Predecessors
1         Null
2         Null
3         1
3         2

Which may have Task C awaiting both Task A and Task B to accomplish.

So my real question is: What's the easiest method to store this type of data and effectively query it? Worthwhile assets with this type of factor? There's a lot of details about tree structures, but when you include multiple parents it might be difficult to find info. Incidentally, I am dealing with SQL Server and .Internet with this task.

Use adjacency list model:


task_id  predecessor
3        1
3        2

which query to locate all forerunners from the given task:

WITH    q AS
        SELECT  predecessor
        FROM    chain
        WHERE   task_id = 3
        UNION ALL
        SELECT  c.predecessor
        FROM    q
        JOIN    chain c
        ON      c.task_id = q.predecessor
FROM    q

To obtain the amount of a long parent for every task:

WITH    q AS
        SELECT  task_id, duration
        FROM    tasks
        UNION ALL
        SELECT  t.task_id, t.duration
        FROM    q
        JOIN    chain с
        ON      c.task_id = q.task_id
        JOIN    tasks t
        ON      t.task_id = c.predecessor
SELECT  task_id, MAX(duration)
FROM    q

Your condition relates to the idea of relationship cardinality. All associations possess some cardinality, which expresses the possibility quantity of instances on both sides from the relationship which are people from it, or can participate in one demonstration of the connection. For example, for individuals, (for many life, I suppose, with rare exceptions), parents-Child relationship includes a cardinality of 2 to zero or many, meaning it requires two parents around the parent side, and there might be zero or many children (possibly it ought to be 2 to 1 or many)

In database design, generally, anything which has a 1(one), (or perhaps a zero a treadmill), on one for reds can be simply symbolized with only two tables, one for every entity, (sometimes just one table is required see note**) along with a foreign key column within the table representing the "many" side, that indicates another table holding the entity around the "one" side.

Inside your situation you've got a many to many relationship. (An Activity might have multiple forerunners, and every forerunners can typically be the predecessor for multiple tasks) Within this situation another table is required, where each row, effectively, signifies a connection between 2 tasks, representing that certain may be the predecessor towards the other. Generally, This table is made to contain only all of the posts from the primary secrets of these two parent tables, and it is own primary secret is an amalgamated of all of the posts both in parent Primary secrets. Inside your situation it really has two posts, the taskId, and also the PredecessorTaskId, which set of Ids ought to be unique inside the table so together they make up the composite PK.

When querying, to prevent double counting data posts within the parent tables when you will find multiple joins, simply base the query around the parent table... e.g., to obtain the amount of a long parent, Presuming your association table is known as TaskPredecessor

  Select TaskId, Max(P.Duration)
  From Task T Join Task P
     On P.TaskId In (Select PredecessorId 
                     From TaskPredecessor
                     Where TaskId = T.TaskId)

** NOTE. In instances where both organizations within the relationship are of the identical entity type, they are able to both maintain exactly the same table. The canonical (luv that word) example is definitely an worker table using the many to 1 relationship of Worker to Supervisor... Because the supervisor can also be an worker, both employees and administrators could be within the same [Worker] table, and also the realtionship can gbe patterned having a Foreign Key (known as say SupervisorId) that indicates another row within the same table and consists of the Id from the worker record for your employee's supervisor.