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