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:
chain 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 ) SELECT * 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.