Table Relation Question

[WorkingMom3]

Let's say a user wants to perform TaskA. In order to completely perform TaskA, TaskB, TaskC, and TaskD need to be completed also and in that order. If the user wants to complete, say, TaskQ, in order to fully complete TaskQ, they will need to completely perform TaskM, TaskN, TaskO, TaskP.

I was thinking something like this:

tblMajorTask
MajorTaskId
TaskName
OtherFields

tblMinorTaskLink
MajorTaskId
MinorTaskId

tblMinorTask
MinorTaskId
TaskName
Other Fields

However, minor tasks can actually be a Major task too...just depends on the user's starting point and their intended goal (e.g. the user could have started with TaskB, then to complete taskB, they would only have to complete TaskC and TaskD). So, I kind of wrote off the idea of Major and Minor tasks. Then I thought...hey...if only a task could have a 1 to Many relationship with iteself...only in my mind I can't see to get a grip on that. Can you do that? Does that work as a design? Are there flaws/downfalls this newbie can't see?

Thanks in advance!

 

[Remou]

These may help:

http://blog.wekeroad.com/2005/04/08/cursed-recursiveness-querying-a-recursive-join/

http://www.sqlteam.com/article/more-trees-hierarchies-in-sql

 

[Remou]

This may be better:

http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

 

[Crowley16]

no reason why a table can't have a many-many relationship with itself...

tasks ( taskID, taskName ... )
taskLink ( thisTaskID, parentTaskID, ... )

you will need some function to link back through tasks to build a tree, but that's pretty standard and you can find examples quite easily...

--------------------
Procrastinate Now!