Table: Task
Columns
NameTypeConstraintsDescription
task_KEYintNOT NULLAUTO-INCREMENT: Database assigned primary key for this table.
update__staff_KEYintNOT NULL
update_datedatetimeNOT NULL
create_datedatetimeNOT 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_datedatetimeNOT NULLOnly completion events recorded after this date 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$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__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_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_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_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