Footprints Data Model

Table
Name asgnmt
Comment The current state and historical record of responsibility for a ticket. Regular assignments involve all parties that share responsibility for the ticket and are interested potentially in notifications. Primary Assignments additionally indicate that one and only one userid is responsible for the ticket for a span of time. A userid will have a span of Primary Assignment that is within the boundaries of his regular non-Primary Assignment. It is expected that an individual would have a single large span of time, possibly the life of the ticket, as a regular assignment. The individual may have zero, one, or more periods of PrimaryAssignment, stored as additional records.

Column
Name Datatype Comment Is PK Is FK
asgnmt_id bigint The automatically generated primary key of the asgnmt table. Yes No
item_defn_id bigint A Foreign Key to the Definition table. Identifies the Ticket Item that the assignments refer to. This is needed if the table is stored as a global table, or workspace table. If it is stored as an item level table, it may not be needed unless union queries are anticipated. No Yes
item_id bigint A Foreign Key to the Item table. No Yes
field_id bigint A Foreign Key to the Definition table. Indicates the field definition for the assignees. This would allow multiple assignees type fields to be used, for example Primary Assignee 1, Primary Assignee 2, Regular Assignees. No Yes
app_user_id bigint A Foreign Key to the Application User table. The technician assignee. No Yes
team_id bigint A Foreign Key to the Team table. The team which can indicate a team level assignment, or the team underneath which the technician is assigned. Examples "Level 1", "Triage", "Web Team". No Yes
cc_email_addr varchar(255) Cannot be used with TeamId,App_user_id, can only be used with CC Assignment. This is an email address. No No
begin_date datetime The begin date of this assignment period, used to determine when a user was assigned. No No
end_date datetime The end date of an assignment. Assignments are not deleted, they are ended so that the historical time period of assignment can easily be queried and reported. If the assignment is not null, then the assignee no longer appears in the current list of assignees. No No
asgnmt_type_id int A Foreign Key to the Assignment Type table. This is a flexible means of recording the assignment type based on an enum. No Yes
is_team bit Special clarifying field of "Is Team Level Assignment" to state that the Team does not have to be immediately resolved to a Team+Individual. No No
delete_txn_id bigint A unique incremental transaction identifier that indicates a soft delete. The id is generated via id generation, and may or may not correspond to the audit trail transaction id. This method of marking the soft delete allows for a unique index to be created on the Workspace Definition Id, Team Name, and Delete Transaction Id to enforce no duplicates in the live copies, and it allows multiple soft deleted rows with the same team name. ** This pattern of soft delete may be helpful for the assignment in order to allow unique indexes. It seems to be redundant to end_date of the assignment. There is no semantic difference between ending an assignment and logically deleting it, it was valid for some period of time. We do not have a feature that allows for hard or soft deletion of historical assignments. No No
sort_order int The display order of the assignment can be preserved with this field if desired. No No