Table: Project_Template
Columns
NameTypeConstraintsDescription
project_template_KEYintNOT NULLAssigned by COMMON_dp_Allocate_Unique_Identifiers. Database assigned primary key for this table.
update__staff_KEYintNOT NULL
update_date_utcdatetimeNOT NULL
create_date_utcdatetimeNOT NULL
project_template_idnvarchar (11)NOT NULLUser defined ID (unique).
descriptionnvarchar (250)NOT NULLUser defined description.
long_descriptionnvarchar (-1)NOT NULLLong description to use on invoices.
engagement_template_KEYintNOT NULLKey to a Engagement Template that will be used for generated projects.
priority_KEYintNOT NULLKey to a Priority that will be used for generated projects.
tracking_description_KEYintNOT NULLKey to a Tracking Description that will be used for generated projects.
responsible__staff_grouping_type_KEYintNOT NULLKey to a Staff Grouping Type that indicates how to determine which staff will be responsible for generated projects.
responsible__staff_KEYintKey to a Staff that will be responsible for generated projects.
assigned__staff_grouping_type_KEYintNOT NULLKey to a Staff Grouping Type that indicates how to determine which staff will be assigned to 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 an event that will mark the generated 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_hoursdecimal (9, 2)NOT NULLNumber of hours budgeted to this project (when budgeted_based_on_tasks = 0).
budgeted_amountdecimal (9, 2)NOT NULLAmount budgeted to this project (when budgeted_based_on_tasks = 0).
row_versiontimestampNOT NULL
department_grouping_type_KEYintNOT NULLKey to a Department Grouping Type that indicates how to determine which department will be assigned to generated projects.
solution_type_namenvarchar (50)NOT NULLName of the type of solution that is used for Projects generated from this Template. The system has a finite set of solution type names defined.
project_template_id_sortablevarchar (48)NOT NULLA representation of the project_template_id column that sorts in natural order, meaning that digits are sorted as numbers while non-digits are sorted alphabetically. A trigger keeps this field synchronized with the content of project_template_id.
staff_assignment_method_KEYintNOT NULL
staff_qualifications__filter_KEYint
budgeted_amount_is_calculatedbitNOT NULL
schedule_item_series_KEYintNOT NULLKey of series that goes with this project template.
extension_eventnvarchar (50)NOT NULLDescription of a "User" type client status event that will cause this [Project] to be extended.
Primary key
NameColumnsDescription
PK_Project_Template$project_template_KEYproject_template_KEYCLUSTERED
Unique constraints
NameColumnsDescription
UK_Project_Template$project_template_idproject_template_idEach id must be unique.
UK_Project_Template$project_template_id_sortableproject_template_id_sortableAn index by the sortable ID is desired, and the algorithm ends up producing unique values.
UK_Project_Template$project_template_KEY$project_template_id$project_template_id_sortableproject_template_KEY, project_template_id, project_template_id_sortableThis is necessary for a foreign key constraint to Project.
UK_Project_Template$schedule_item_series_KEYschedule_item_series_KEYEach Project Templste must have its own Series.
Indexes
NameColumnsDescription
IX_Project_Template$assigned__staff_grouping_type_KEYassigned__staff_grouping_type_KEYIndex supports foreign key FK_Project_Template__Staff_Grouping_Type__assigned__staff_grouping_type_KEY
IX_Project_Template$department_grouping_type_KEYdepartment_grouping_type_KEYIndex supports foreign key FK_Project_Template__Department_Grouping_Type__department_grouping_type_KEY
IX_Project_Template$department_KEYdepartment_KEYIndex supports foreign key FK_Project_Template__Department__department_KEY
IX_Project_Template$engagement_template_KEYengagement_template_KEYIndex supports foreign key FK_Project_Template__Engagement_Template__engagement_template_KEY
IX_Project_Template$priority_KEYpriority_KEYIndex supports foreign key FK_Project_Template__Priority__priority_KEY
IX_Project_Template$responsible__staff_grouping_type_KEYresponsible__staff_grouping_type_KEYIndex supports foreign key FK_Project_Template__Staff_Grouping_Type__responsible__staff_grouping_type_KEY
IX_Project_Template$responsible__staff_KEYresponsible__staff_KEYIndex supports foreign key FK_Project_Template__Staff__responsible__staff_KEY
IX_Project_Template$staff_assignment_method_KEYstaff_assignment_method_KEYIndex supports foreign key FK_Project_Template__Staff_Assignment_Method__staff_assignment_method_KEY
IX_Project_Template$staff_qualifications__filter_KEYstaff_qualifications__filter_KEYIndex supports foreign key FK_Project_Template__Filter__staff_qualifications__filter_KEY
IX_Project_Template$tracking_description_KEYtracking_description_KEYIndex supports foreign key FK_Project_Template__Tracking_Description__tracking_description_KEY
Foreign keys
NameColumnsReferenced tableDescription
FK_Project_Template__Department__department_KEYdepartment_KEYDepartment
FK_Project_Template__Department_Grouping_Type__department_grouping_type_KEYdepartment_grouping_type_KEYDepartment_Grouping_Type
FK_Project_Template__Engagement_Template__engagement_template_KEYengagement_template_KEYEngagement_Template
FK_Project_Template__Filter__staff_qualifications__filter_KEYstaff_qualifications__filter_KEYFilter
FK_Project_Template__Priority__priority_KEYpriority_KEYPriority
FK_Project_Template__Schedule_Item_Series__schedule_item_series_KEYschedule_item_series_KEYSchedule_Item_Series
FK_Project_Template__Staff__responsible__staff_KEYresponsible__staff_KEYStaffon delete set null;
FK_Project_Template__Staff_Grouping_Type__assigned__staff_grouping_type_KEYassigned__staff_grouping_type_KEYStaff_Grouping_Type
FK_Project_Template__Staff_Grouping_Type__responsible__staff_grouping_type_KEYresponsible__staff_grouping_type_KEYStaff_Grouping_Type
FK_Project_Template__Tracking_Description__tracking_description_KEYtracking_description_KEYTracking_Description
Incoming foreign keys
NameColumnsReferencing tableDescription
FK_Project_Template_Assignment__Project_Template__project_template_KEYproject_template_KEYProject_Template_Assignment
FK_Project_Template_Entity__Project_Template__project_template_KEYproject_template_KEYProject_Template_Entity
FK_Project_Template_Extension__Project_Template__project_template_KEYproject_template_KEYProject_Template_Extension
FK_Task_Template__Project_Template__project_template_KEYproject_template_KEYTask_Template
FK_Project__Project_Template__project_template_KEYproject_template_KEYProject
FK_Project__Project_Template__project_template_KEY__project_template_id__project_template_id_sortableproject_template_KEY, project_template_id, project_template_id_sortableProjecton update cascade;

Schema diagram