Table: Task
Columns
NameTypeConstraintsDescription
task_KEYintNOT NULLAUTO-INCREMENT: Database assigned primary key for this table.
update__staff_KEYintNOT NULL
project_KEYintNOT NULLKey of the Project this Task belongs to.
completion_orderintNOT NULLThe user defined order the tasks are to be completed in (not unique).
budgeted_amountdecimal (9, 2)NOT NULLAmount budgeted to this task.
actual_start_datesmalldatetimeThe actual start date.
actual_complete_datesmalldatetimeThe actual complete date.
targetnvarchar (1000)NOT NULLTarget to execute. File name or url.
completion_eventnvarchar (50)NOT NULLDescription of a "User" type client status event that will mark this Task as being complete.
row_versiontimestampNOT NULL
solution_type_namenvarchar (50)NOT NULLName of the type of solution that is used for this Task. The system has a finite set of solution type names defined.
completion_event_recorded_date_utcdatetimeNOT NULLOnly completion events recorded after this date (in UTC) are eligible to complete this Task. When the completion event is used to complete the Task this value must be updated.
task_numberintNOT NULLEach Task will be assigned a number that is unique that the user can select.
project_template_KEYintNOT NULLKey of the Project Template of the Project this Task belongs to, copied here so that Declarative Referential Integrity can be used between Task and Task_Template.
schedule_item_KEYintNOT NULL
budgeted_amount_is_calculatedbitNOT NULL
task_template_KEYintThe linked [Task_Template].
create__changeset_KEYintNOT NULL
update__changeset_KEYintNOT NULL
Primary key
NameColumnsDescription
PK_Task$task_KEYtask_KEYCLUSTERED
Unique constraints
NameColumnsDescription
UK_Task$project_template_KEY$task_KEYproject_template_KEY, task_KEYThis unique constraint supports the relationship from Project to Task to Task_Template where a Task_Template assigned to a Task must also be assigned the same Project_Template as the Project.
UK_Task$schedule_item_KEYschedule_item_KEY
UK_Task$task_KEY$project_KEYtask_KEY, project_KEYThis unique constraint supports the fact that if a Task is assigned to a Sheet Entry the Task must be for the same Project as the Project assigned to the Sheet Entry.
UK_Task$task_KEY$schedule_item_KEYtask_KEY, schedule_item_KEYThis unique constraint supports the relationship between task_KEY and task__schedule_item_KEY on Sheet_Entry.
UK_Task$task_numbertask_numberEach Task is assigned a unique number that the user should have some control over.
Indexes
NameColumnsDescription
IX_Task$project_KEY$project_template_KEYproject_KEY, project_template_KEYIndex supports foreign key FK_Task__Project__project_KEY__project_template_KEY
IX_Task$project_KEY@completion_order@schedule_item_KEY@task_KEYproject_KEY, completion_order, schedule_item_KEY, task_KEYCovers the columns needed to compute whether a task is ready or not depending on the completion_order of tasks for a project.
IX_Task$project_template_KEY$task_template_KEYproject_template_KEY, task_template_KEYIndex supports foreign key FK_Task__Task_Template__project_template_KEY__task_template_KEY
IX_Task$schedule_item_KEY$project_KEYschedule_item_KEY, project_KEYSupport getting Schedule_Item_View.current_due_date efficiently.
IX_Task$task_template_KEYtask_template_KEY
Foreign keys
NameColumnsReferenced tableDescription
FK_Task__Project__project_KEYproject_KEYProject
FK_Task__Project__project_KEY__project_template_KEYproject_KEY, project_template_KEYProjecton delete cascade;
FK_Task__Schedule_Item__schedule_item_KEYschedule_item_KEYSchedule_Item
FK_Task__Task_Template__project_template_KEY__task_template_KEYproject_template_KEY, task_template_KEYTask_Template
FK_Task__Task_Template__task_template_KEYtask_template_KEYTask_Templateon delete set null;
Incoming foreign keys
NameColumnsReferencing tableDescription
FK_Sheet_Entry__Task__task_KEYtask_KEYSheet_Entry
FK_Sheet_Entry__Task__task_KEY__project_KEYtask_KEY, project_KEYSheet_Entry
FK_Sheet_Entry__Task__task_KEY__task__schedule_item_KEYtask_KEY, task__schedule_item_KEYSheet_Entry

Schema diagram