Subject Area : 141 - Calendar Appointments and Items
Subject Area Name Definition
141 - Calendar Appointments and Items The tables used to define the Workspace Calendar and associated appointments as iCal items. These tables are accessed exclusively through the Calendar service API.
ER Diagram Name Physical Display Level
Logical Column
Physical Column
Physical Name Entity/Table Name Logical Only Do Not Generate Definition
app_user Application User false A user authorized to use the FootPrints system. The user is either a Customer User or an Agent User. The application user holds the minimum information needed to identify the user and associate the user with a user profile, where user settings are stored. A contact is not necessarily an application user. An application user entry is required in order for the user to access any online functions.
Physical Name Attribute/Column Name Definition Physical Data Type
pwd Password This space intentionally left blank. varchar(255)
user_name User Name The name of the Application User. nvarchar(4000)
cust_login_type_id Customer Login Type Id A Foreign Key to the Customer Login Type table. int
app_user_id Application User Id The automatically generated primary key of the app_user table. bigint
lic_type_id License Type Id A Foreign Key to the License Type table. bigint
user_login User Login The login name used to access the Application. This may not be filled in if external application security is used. nvarchar(4000)
email_addr Email Address An email address for the Application User. nvarchar(4000)
delete_date Delete Date The value of a Application User, stored as a date. datetime
cal_coll_item Calendar Collection Item false Holds calendar item collection relationships
Physical Name Attribute/Column Name Definition Physical Data Type
create_date create date create date, stored as a long integer to avoid using date time classes that do not handle timezone well. int
reln_type Relation Type The value of a Calendar Collection Item, stored as a string. varchar(16)
coll_cal_item Collection Calendar item A Foreign Key to the Calendar Item table. bigint
cal_item_id Calendar Item Id A Foreign Key to the Calendar Item table. bigint
cal_cntnr_item Calendar Container Item false The Container Item that is related to the Calendar Appointment Item. For Workspaces, the Workspace default calendar will have appointments between Issue Assignee agents and the Customer Contact in order to resolve the Issue/Workspace Item. It is called Container Item in case there is a capability to relate the appointment to a CMDB item or Knowledgebase Item.
Physical Name Attribute/Column Name Definition Physical Data Type
cal_cntnr_item_id Calendar Container Item Id The automatically generated primary key of the cal_cntnr_item table. bigint
item_defn_id Item Definition Id A Foreign Key to the Definition table. Identifies the Container Definition which in turn identifies the dynamic object and table that contains the Item. This is the first part of a two-part identifier for the dynamic item. bigint
item_id Item Id A logical Foreign Key to the Item table. It is an actual primary key in the dynamic object and table that contains the Item. This is the second part of a two-part identifier for the dynamic item. bigint
appt_cal_item_id Appointment Calendar Item Id A Foreign Key to the Calendar Item table. This is an appointment type of item that has been set up related to the Workspace/Container Item. bigint
field_defn_id Field Definition Id A Foreign Key to the Definition table. bigint
is_original Is Original Indicates if this is the original container for the item. bit
occurence_uid Occurence UID The value of a Calendar Container Item, stored as a string. varchar(255)
cal_content_data Calendar Content Data false Binary Large Objects (BLOBs) linked to an appointment item.
Physical Name Attribute/Column Name Definition Physical Data Type
cal_content_data_id Calendar Content Data Id The automatically generated primary key of the cal_content_data table. bigint
content_blob content_blob OID
cal_event_stamp Calendar Event Stamp false A log of calendar service events. A stamp associates a related set of properties/attributes/methods to an Calendar Item. Event Stamp contains properties and apis relating to an iCal event.
Physical Name Attribute/Column Name Definition Physical Data Type
ical_data ical data The value of a Calendar Event Stamp, stored as a long text object. varchar(max)
end_date end date end date. varchar(255)
is_floating is floating Indicates if is floating. bit
is_recurring is recurring Indicates if is recurring. bit
start_date start date start date. varchar(255)
cal_stamp_id Calendar Stamp Id A Foreign Key to the Calendar Stamp table. bigint
cal_field Calendar Field false A name/value data field value for a calendar item. Calendar Item has a map of Attribute objects, indexed by a QName . An Attribute can be associated to a single Item . QName is a composite key that represents a qualified name (namespace and local name). There are several Attribute implementations (String, Binary, Integer, etc) that are used to store different types of values. The polymorphic mapping allows any number of and any mix of Attribute implementations to be associated to an item.
Physical Name Attribute/Column Name Definition Physical Data Type
attr_type attribute type attribute type. varchar(255)
cal_field_id Calendar Field Id The automatically generated primary key of the cal_field table. bigint
create_date create date create date, stored as a long integer to avoid using date time classes that do not handle timezone well. int
etag etag etag. varchar(255)
mdfy_date modify date modify date, stored as a long integer to avoid using date time classes that do not handle timezone well. int
local_name local name local name. varchar(25)
nmsp namespace namespace. varchar(220)
binary_val binary value Object Identifier binary value OID
boolean_val boolean value generic boolean value. bit
date_val date value generic date value datetime
tz_val tz value time zone value. varchar(255)
decimal_val decimal value generic decimal value numeric(19,6)
text_val text value The value of a Calendar Field, stored as a long text object. varchar(max)
integer_val integer value The value of a Calendar Field stored as Long. bigint
string_val string value string value. varchar(255)
cal_item_id Calendar Item Id A Foreign Key to the Calendar Item table. bigint
cal_field_dict_val Calendar Field Dictionary Value false Stores dictionary values for a calendar item. May not be used.
Physical Name Attribute/Column Name Definition Physical Data Type
cal_field_id Calendar Field Id A Foreign Key to the Calendar Field table. bigint
string_val string value string value. varchar(2048)
val_key value_key value_key. varchar(255)
cal_field_multistring_val Calendar Field Multistring Value false Stores multiple string values for a calendar item. May not be used. Has no primary key, its a bag of values.
Physical Name Attribute/Column Name Definition Physical Data Type
cal_field_id Calendar Field Id A Foreign Key to the Calendar Field table. bigint
string_val string value string value. varchar(2048)
cal_item Calendar Item false The main calendar item table for the calender service. This table is populated and maintained by the Calendar Service API. It is not very suitable for database query outside of the API. The calendar item can be a personal or workspace calendar, or an appointment within the calendar. The calendar item can be synched using the iCal format.
Physical Name Attribute/Column Name Definition Physical Data Type
item_type item type item type. varchar(16)
cal_item_id Calendar Item Id The automatically generated primary key of the cal_item table. bigint
create_date create date create date, stored as a long integer to avoid using date time classes that do not handle timezone well. int
etag etag etag. varchar(255)
mdfy_date modify date modify date, stored as a long integer to avoid using date time classes that do not handle timezone well. int
client_create_date client create date client create date, stored as a long integer to avoid using date time classes that do not handle timezone well. int
client_mod_date client modified date client modified date, stored as a long integer to avoid using date time classes that do not handle timezone well. int
display_name display name display name. nvarchar(4000)
item_name item name item name. nvarchar(4000)
intrnl_id Internal Id Internal Id. varchar(255)
last_modf last modification The value of a Calendar Item, stored as a date. int
last_mod_by last modified by last modified by. nvarchar(4000)
needs_reply needs reply Indicates if needs reply. bit
sent sent Indicates if sent. bit
ical_uid ical uid ical uid. varchar(255)
content_encoding content encoding content encoding. varchar(32)
content_language content language content language. varchar(32)
content_length content length The value of a Calendar Item stored as Long. bigint
content_type content type content type. varchar(64)
has_modfs has modifications Indicates if has modifications. bit
cal_content_data_id Calendar Content Data Id A Foreign Key to the Calendar Content Data table. bigint
app_user_id Application User Id A Foreign Key to the Application User table. bigint
modifies_cal_item_id Modifies Calendar Item Id A Foreign Key to the Calendar Item table. bigint
delete_date Delete Date The value of a Calendar Item, stored as a date. datetime
cal_schd Calendar Schedule false false The association between the FootPrints calendar instance, and the implementation of the calendar schedule and appointments. This enables the reuse of a schedule between workspaces, but it does not require it.
Physical Name Attribute/Column Name Definition Physical Data Type
schd_id Schedule Id A Foreign Key to the Schedule table. bigint
cal_schd_id Calendar Schedule Id The automatically generated primary key of the cal_schd table. bigint
cal_item_id Calendar Item Id A Foreign Key to the Calendar Item table. bigint
cal_stamp Calendar Stamp false Calendar Item has a set of Calendar Stamp objects. A Calendar stamp can be associated to a single Calendar Item . A stamp associates a related set of properties/attributes/methods to an Calendar Item. This is different from Calendar Attribute , which generally represents a single value. For example Event Stamp , contains properties and apis relating to an event and MessageStamp contains properties related to an email message. The stamp model allows items to be stamped and unstamped with these related sets of properties throughout its life-cycle.
Physical Name Attribute/Column Name Definition Physical Data Type
stamp_type stamp type stamp type. varchar(16)
cal_stamp_id Calendar Stamp Id The automatically generated primary key of the cal_stamp table. bigint
create_date create date create date, stored as a long integer to avoid using date time classes that do not handle timezone well. int
etag etag etag. varchar(255)
mdfy_date modify date modify date, stored as a long integer to avoid using date time classes that do not handle timezone well. int
cal_item_id Calendar Item Id A Foreign Key to the Calendar Item table. bigint
cal_subscription Calendar Subscription false A Calendar Subscription contains a ticket key and a collection uid. It is a way to associate a collection that is not owned by the user to the user. The ticket key provides the necessary authentication to access the collection. Not used.
Physical Name Attribute/Column Name Definition Physical Data Type
cal_subscription_id Calendar Subscription Id The automatically generated primary key of the cal_subscription table. bigint
create_date create date create date int
etag etag etag varchar(255)
mdfy_date modify date modify date int
coll_uid collection uid id of the collection varchar(255)
ticket_key ticket key permissions varchar(255)
display_name display name Name of the calendar in the UI varchar(255)
app_user_id Application User Id A Foreign Key to the Application User table. bigint
cal_ticket Calendar Ticket false This is unrelated and different than an ITSM ticket. See the Calendar Container Item table for ITSM tickets. A calendar ticket is an authorization object that grants some level of access to a calendar item and its descendents based on a unique ticket key. If a client has this key, then the client can gain access to the item and its descendants with the ticket key. May not be used in the FootPrints integration of calendaring code, because the calendar permissions are subordinate to the application security model.
Physical Name Attribute/Column Name Definition Physical Data Type
cal_ticket_id Calendar Ticket Id The automatically generated primary key of the cal_ticket table. bigint
create_date Create Date Create date. int
etag etag The value of a Calendar Ticket, stored as a string. varchar(255)
mdfy_date Modify Date Change date. int
creatn_date Creation Date Create date. datetime
ticket_key Ticket Key Authorization id. varchar(255)
app_user_id Application User Id A Foreign Key to the Application User table. bigint
ticket_timeout Ticket Timeout Expiration of authority. varchar(255)
cal_item_id Calendar Item Id A Foreign Key to the Calendar Item table. bigint
cal_ticket_privilege Calendar Ticket Privilege false (Not used, but referenced in Java code that must be removed before table deletion.) A permission granted via a calendar ticket. May not be used in the FootPrints integration of calendaring code.
Physical Name Attribute/Column Name Definition Physical Data Type
cal_ticket_id Calendar Ticket Id A Foreign Key to the Calendar Ticket table. bigint
privilege Privilege The value of a Calendar Ticket Privilege, stored as a string. varchar(255)
cal_wksp Calendar Workspace false false The association of a workspace to a calendar. The workspace definition is associated with the Calendar Item that is the root entry for a recurring schedule item in the calender service tables. The workspace can have one default work schedule, and it may have a second shift.
Physical Name Attribute/Column Name Definition Physical Data Type
cal_wksp_id Calendar Workspace Id The automatically generated primary key of the cal_wksp table. bigint
wksp_defn_id Workspace Definition Id A Foreign Key to the Definition table. Identifies the Workspace that owns the Calendar. bigint
cal_item_id Calendar Item Id A Foreign Key to the Calendar Item table. This is a Workspace Calendar type of Calendar Item that owns appointments. bigint
is_dflt_flag Is Default Flag Indicates if Is Default Flag. bit
defn Definition false A definition holds the structural and behavioral information needed for an application element that is configuarable by the system designer and/or the end user at definition time. Current Footrpints uses a combination of directories, files, tables, and programming code to represent this information. The new FootPrints will provide all of the same information within the definition. The definition can be persisted in a structure that is isolated at the Instance Level. The data for the definition will be stored as Configuration Settings associated with the Definition Version. See the database views v_defn_ver_active, v_cntnr_item_field.
Physical Name Attribute/Column Name Definition Physical Data Type
defn_id Definition Id The automatically generated primary key of the defn table. bigint
defn_name Definition Name The name of the Definition. nvarchar(4000)
dsble_date Disabled Date The date that this instance of Definition is logically deleted and disabled. datetime
defn_guid Definition GUID A Global Unique IDentifier search key for the Definition, composed of letters and numbers. It is computed by the client and typically used in unstructured data. varchar(50)
defn_type_name Definition Type Name The unique string identifier for an instance of Definition Type. This identifier must be kept in sync with programming code that uses an enumeration constant to identify particular values. A scan program is generally used to populate this table. varchar(255)
defn_subtype_id Definition Subtype Id A Foreign Key to the Definition Subtype table. int
tmpl_export_guid Template Export GUID The value of a Definition, stored as a string. VARCHAR(50)
item Item true true The Item is the abstract class for Issue, Configuration Item, Address Book entry, and other information items that are the main information entities in FootPrints. The Item structure and behavior is defined by its Definition. The Item is namespaced, identified by, and logically stored in the single parent container. It can be referenced in the same manner both inside and outside the container. The Item is the storage model and the implementation for the Item Definition. There are many important specializations of the Item class, and there even more Template definitions of Container/Item that provide an end-user application experience to the End-User. The Item implementation must implement flexible schema in an easy to manage, well engineered, and high performing way. The item can store user-defined and user-named fields defined in its definition. The Item could implement the nice-to-have features such as an inheritance model for Item subtypes, and have the ability to represent collections of subordinate items. The Item data can be imported, exported, or synchronized with external data sources. The Item data can be linked and synchronized with other items in the same Instance.
Physical Name Attribute/Column Name Definition Physical Data Type
item_key Item Key A search key for the Item, composed of letters and numbers. varchar(255)
item_id Item Id The automatically generated primary key of the item table. bigint
cntnr_id Container Id A Foreign Key to the Container table. bigint
item_defn_id Item Definition Id A Foreign Key to the Definition table for an instance of type Definition. bigint