How could I design this database scenario?

Question

I am trying to create a "Task" schema in my database. One field is "blockedBy" which represents another Task that is blocking this current task.

I am new to SQL Server and databases in general. How can I create the table properly? It seems like the only valid options are numbers or text.

Should I just store the ID number of the other "Task" in the "blockedBy" field or is there a better way?

Answer 1

If there can be multiple blocking tasks associated with one task you need to introduce another table, named such as "BlockingTasks" which has the ID of the task and that of the ID of the task that blocks it. Don't do it as CVS as that is difficult to do queries on.

Answer 2

I assume:

• you have a schema called "Tasks" in a database in SQL Server
• you then have a table (say, AllTasks) that stores task information
• you would like to have a field called blockedby that indicates which existing task is blocking the current task

Here's my proposal of columns, and an example of how the data would look like:

TaskID   TaskName    TaskDescription .....   TaskBlockedBy
------   --------    ---------------         -------------
1        Eat         Eat food                <NULL>
2        Drink       Drink, um, water        <NULL>
3        Sleep       Ah, peace!              <NULL>
4        Wake Up     Crap                    3

Wake up task in row 4 is blocked by Sleep task in Row 3.

Table fields will have datatype:

TaskID int (identity/autonumber)
TaskName nvarchar(50) not null
TaskDescription nvarchar(200) not null
TaskBlockedBy int

If you anticipate multiple existing tasks blocking a task, break up the table into 2 tables: AllTasks and BlockedTasks. AllTasks table will not have TaskBlockedBy field anymore and BlockedTasks and its data will look like this:

BlockID  TaskID  BlockedBy
-------  ------- ----------
1        4       3
2        4       2

This indicates that task of Wake Up was blocked by Sleep and what the user drank before sleeping.

HTH