Table: Project
Columns
NameTypeConstraintsDescription
project_KEYintNOT NULLAUTO-INCREMENT: Database assigned primary key for this table.
update__staff_KEYintNOT NULL
schedule_item_KEYintNOT NULLThe [Schedule_Item] that owns this link.
project_numberintNOT NULLUser defined number (unique).
long_descriptionnvarchar (-1)NOT NULLLong description to use on invoices.
responsible__staff_KEYintKey to a Staff that will be responsible for generated projects.
department_KEYintKey to a Department that will be used for generated projects.
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 Project as being complete.
budgeted_based_on_tasksbitNOT NULLWhen set to 1 will indicate that the budget is entirely based off the budget of the Tasks for the generated project.
budgeted_amountdecimal (9, 2)NOT NULLAmount budgeted to this project (when budgeted_based_on_tasks = 0).
percent_completedecimal (3, 2)NOT NULLPercent complete as a decimal (0 to 1.00).
received_datesmalldatetimeDate the information required to begin the project was recieved.
actual_start_datesmalldatetimeThe actual start date.
actual_complete_datesmalldatetimeThe actual complete date.
row_versiontimestampNOT NULL
project_template_KEYintNOT NULLKey to the Project Template that was used to generate this Project.
original_due_datesmalldatetimeNOT NULLThe original due date of the project; readonly once the record is created.
extension_numberintNOT NULLNumber of the current extension. 0=not extended, 1=first extension, etc. We expect to find a record in dbo.Project_Extension with this project_KEY and extension_number but not absolutely required (weak relationship).
solution_type_namenvarchar (50)NOT NULLName of the type of solution that is used for this Project. 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 Project. When the completion event is used to complete the Project this value must be updated.
budgeted_amount_is_calculatedbitNOT NULL
percent_complete_is_overriddenbitNOT NULL
extension_eventnvarchar (50)NOT NULLDescription of a "User" type client status event that will cause this [Project] to be extended.
project_template_idnvarchar (11)NOT NULLCached value of the project_template_id for the associated Project template.
project_template_id_sortablevarchar (48)NOT NULLCached value of the project_template_id_sortable for the associated Project template.
gofileroom_workflow_KEYintGoFileRoom Workflow associated with this Project.
create__changeset_KEYintNOT NULL
update__changeset_KEYintNOT NULL
Primary key
NameColumnsDescription
PK_Project$project_KEYproject_KEYCLUSTERED
Unique constraints
NameColumnsDescription
UK_Project$project_KEY$project_template_KEYproject_KEY, project_template_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_Project$project_KEY$schedule_item_KEYproject_KEY, schedule_item_KEYThis unique constraint supports the relationship between project_KEY and project__schedule_item_KEY on Sheet_Entry.
UK_Project$project_numberproject_number
UK_Project$project_template_KEY$project_KEYproject_template_KEY, project_KEY
UK_Project$schedule_item_KEYschedule_item_KEY
Indexes
NameColumnsDescription
IX_Project$department_KEYdepartment_KEYIndex supports foreign key FK_Project__Department__department_KEY
IX_Project$project_template_idproject_template_id
IX_Project$project_template_id_sortableproject_template_id_sortable
IX_Project$project_template_KEY$project_template_id$project_template_id_sortableproject_template_KEY, project_template_id, project_template_id_sortableIndex supports foreign key FK_Project__Project_Template__project_template_KEY__project_template_id__project_template_id_sortable
IX_Project$responsible__staff_KEYresponsible__staff_KEYIndex supports foreign key FK_Project__Staff__responsible__staff_KEY
Foreign keys
NameColumnsReferenced tableDescription
FK_Project__Department__department_KEYdepartment_KEYDepartment
FK_Project__Gofileroom_Workflow__gofileroom_workflow_KEYgofileroom_workflow_KEYGofileroom_Workflow
FK_Project__Project_Template__project_template_KEYproject_template_KEYProject_Template
FK_Project__Project_Template__project_template_KEY__project_template_id__project_template_id_sortableproject_template_KEY, project_template_id, project_template_id_sortableProject_Templateon update cascade;
FK_Project__Schedule_Item__schedule_item_KEYschedule_item_KEYSchedule_Item
FK_Project__Staff__responsible__staff_KEYresponsible__staff_KEYStaff
Incoming foreign keys
NameColumnsReferencing tableDescription
FK_Project_Extension__Project__project_KEYproject_KEYProject_Extensionon delete cascade;
FK_Task__Project__project_KEYproject_KEYTask
FK_Task__Project__project_KEY__project_template_KEYproject_KEY, project_template_KEYTaskon delete cascade;
FK_Sheet_Entry__Project__project_KEYproject_KEYSheet_Entry
FK_Sheet_Entry__Project__project_KEY__project__schedule_item_KEYproject_KEY, project__schedule_item_KEYSheet_Entry

Schema diagram