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