Subject Area : 140 - Workspace Calendar and Operation Schedule
Subject Area Name Definition
140 - Workspace Calendar and Operation Schedule The tables used to define the Workspace Calendar aka the Work Schedule. The work schedule is stored as an iCal availability item, and it can be shared across workspaces. These tables simplify the data for reporting.
ER Diagram Name Physical Display Level
Logical Column
Physical Column
Physical Name Entity/Table Name Logical Only Do Not Generate Definition
agent Agent true true a user authorized to use the FootPrints agent functions, which enable the agent to provide services to the customer. These include the ability to edit, escalate, and close tickets.
Physical Name Attribute/Column Name Definition Physical Data Type
lic_type_id License Type Id A Foreign Key to the License Type table. bigint
app_user_id Application User Id A Foreign Key to the Application User table. bigint
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_type Calendar Type false The type of Calendar, Workspace or Personal
Physical Name Attribute/Column Name Definition Physical Data Type
cal_type_name Calendar Type Name The name of the Calendar Type. varchar(255)
cal_type_id Calendar Type Id The automatically generated primary key of the cal_type table. int
schd Schedule false false A personal or workspace calendar in Footprints. It is used to provide easy access to work hours and settings for appointment scheduling and metric reporting. The Calendar Service maintains the system of record tables for the schedule, but those tables are too difficult and time consuming to query for trend reporting.
Physical Name Attribute/Column Name Definition Physical Data Type
schd_id Schedule Id The automatically generated primary key of the schd table. bigint
schd_name Schedule Name The name of the Schedule. varchar(255)
max_date_val Maximum Date Value The time spans are forecast out into the future as CalendarActualPeriod. This is the maximum date for which those periods are generated. datetime
time_zone_region_name Time Zone Region Name The name of the Time Zone Region for the calendar, which derives from or overrides the value used for the system or workspace. varchar(255)
cal_type_id Calendar Type Id A Foreign Key to the Calendar Type table. int
app_user_id Application User Id A Foreign Key to the Application User table. bigint
use_local_time Use Local Time Indicates that the workschedule is base on local time for the time zone region. If you want UTC time, use that as the region. bit
schd_actual_period Schedule Actual Period false Given the definitions of the standard work periods and holidays, these are the work periods. If overlapping shift work periods are defined, the time ranges here represent all shifts. This makes it easier to do math against working hours. The data in this table is time zone accurate for the schedule in the time zone of the workspace. Each minute within the begin and end date of the actual period must be a work minute. The master copy of the schedule is an iCal availability item stored in the Calendar service Calendar Item tables via the API. The data is redundantly and more conveniently stored so that the dashboard can use the information in the calculation of work hours elapsed.
Physical Name Attribute/Column Name Definition Physical Data Type
schd_actual_period_id Schedule Actual Period Id The automatically generated primary key of the schd_actual_period table. bigint
begin_date Begin Date Begin Date. datetime
end_date End Date End Date. datetime
work_durn_in_mins Work Duration In Minutes Work Time duration in minutes. This is the number of work minutes within the begin and end date of the period, which is equal to the end date minus the begin date, because the work periods can be sparsely populated. This value can be summed to get the total work minutes for a date range. If a date marker requested falls within the begin and end date of the actual period, then the minutes from the marker date and the begin/end point must be subtracted from this number. int
schd_id Schedule Id A Foreign Key to the Schedule table. bigint
cum_work_durn_in_mins Cumulative Work Duration In Minutes Cumulative Work Time duration in minutes. This is the number of work minutes from the actual first period to the end of the current actual period as calculated by a running total or cumulative sum of the Work Duration field. Because the work periods will be sparsely populated, the hard dates cannot be subtracted to yield Work Duration Difference. However, this Cumulative Work Duration can be subtracted to get the Work Duration difference. If a date marker requested falls within the begin and end date of the actual period, then the minutes from the marker date and the begin/end point must be subtracted from this number. int
begin_date_utc_s Begin Date UTC S A string version of the UTC date that is used in order to error correct JDBC/Hibernate persistence of the date which produces errors on Daylight Savings Time boundaries. varchar(23)
end_date_utc_s End Date UTC S A string version of the UTC date that is used in order to error correct JDBC/Hibernate persistence of the date which produces errors on Daylight Savings Time boundaries. varchar(23)
time_zone_local_hour Time Zone Local Hour false A bridge table that provides the UTC begin and End Hour datetimes for a local hour in an Olson timezone region. This table is used in Trend Reporting to give a high performance date calculation so that local hours and days can be used on graph axes and report heading with calculated totals. It is also used to load the summary tables using the local time dimension. High speed correct database-side time zone conversions are made by joining to this table.
Physical Name Attribute/Column Name Definition Physical Data Type
begin_date_hour_utc Begin Date Hour UTC A date whole hour in the local timezone for the Begin Date Hour UTC. The Minutes portion of the date will always be 00. It is not stated whether daylight savings is in effect. datetime
end_date_hour_utc End Date Hour UTC A date whole hour in the local timezone for the End Date Hour UTC. The Minutes portion of the date will always be 00. It is not stated whether daylight savings is in effect. datetime
begin_date_hour_local Begin Date Hour Local A date time in UTC that cooresponds to a whole hour date in the local timezone for the Begin Date Hour Local. The Minutes portion of the date will usually be 00 but can be 30, 15, 45 in the case of half hour or quarter hour time zones. UTC never has daylight savings is in effect, datetime
end_date_hour_local End Date Hour Local A date time in UTC that cooresponds to a whole hour date in the local timezone for the End Date Hour Local. The Minutes portion of the date will usually be 00 but can be 30, 15, 45 in the case of half hour or quarter hour time zones. UTC never has daylight savings is in effect, datetime
begin_is_daylight_savings Begin Is Daylight Savings Flag indicating if daylight savings is in effect for the begin time. This helps identify the special hours that are missing or duplicated during daylight savings days. bit
time_zone_region_id Time Zone Region Id An alternate Key to the Time Zone Region table, which is arbitrarily calculated and assigned by FootPrints. The reason for this is performance of the Foreign Key join to large numbers of rows. There is no suitable short data length key available in a published standard data source. bigint
time_zone_local_hour_id Time Zone Local Hour Id The automatically generated primary key of the time_zone_local_hour table. bigint
begin_date_hour_local_s Begin Date Hour Local S A string version of the local date that is used in order to error correct JDBC/Hibernate persistence of the date which produces errors on Daylight Savings Time boundaries. varchar(23)
end_date_hour_local_s End Date Hour Local S A string version of the local date that is used in order to error correct JDBC/Hibernate persistence of the date which produces errors on Daylight Savings Time boundaries. varchar(23)
begin_date_hour_utc_s Begin Date Hour UTC S A string version of the UTC date that is used in order to error correct JDBC/Hibernate persistence of the date which produces errors on Daylight Savings Time boundaries. varchar(23)
end_date_hour_utc_s End Date Hour UTC S A string version of the UTC date that is used in order to error correct JDBC/Hibernate persistence of the date which produces errors on Daylight Savings Time boundaries. varchar(23)
end_is_daylight_savings End Is Daylight Savings Flag indicating if daylight savings is in effect for the end time. This helps identify the special hours that are missing or duplicated during daylight savings days. bit
time_zone_region Time Zone Region false A region of the world defined in the Olson time zone database. Only the entries actually used by a FootPrints instance will be stored. The localized name of the region is provided by the Java.Util.Timezone getDisplayName() method. Only the java class can be used to determine the time zone offset from GMT, which varies historically and in the future. See the Time Zone Local Hour table. Take aspirin as needed.
Physical Name Attribute/Column Name Definition Physical Data Type
time_zone_region_name Time Zone Region Name The name of the Time Zone Region. This is 2. The 'primary key' of the java timezone is the string region identifier in English, example 'America/Phoenix'. There is no numeric identifier. As the JRE is updated, or specific classes are updated, the list could change to include new entries, and the detail data behind the regions could change. varchar(255)
time_zone_region_abbr Time Zone Region Abbreviation A well-known string identifier used as a code to identify the Time Zone Region. This could be provided by user entry, but it is not available from the Timezone class. It should probably not be used. varchar(255)
time_zone_region_id Time Zone Region Id An alternate Key to the Time Zone Region table, which is arbitrarily calculated and assigned by FootPrints. The reason for this is performance of the Foreign Key join to large numbers of rows. There is no suitable short data length key available in a published standard data source. bigint
wksp_cntnr Workspace Container true true A workspace is the principle architectural structure in Numara FootPrints for storing and tracking information. A Workspace is a separate sub-database or work area within the system with its own fields, forms, options, workflow, settings and users. Most of the behaviour of the Workspace comes from the Container Abstract class, and only things that are specific to the Workspace are implemented here. For Example, Current FootPrints allows a single CMDB to be referenced from a Workspace, and a single Address book, yet these are listed as features for improvement. So convenience methods that get and set Container Group Relationship might be appropriate.

Physical Name Attribute/Column Name Definition Physical Data Type
time_zone_region_name Time Zone Region Name The name of the Time Zone Region. This is 2. The 'primary key' of the java timezone is the string region identifier in English, example 'America/Phoenix'. There is no numeric identifier. As the JRE is updated, or specific classes are updated, the list could change to include new entries, and the detail data behind the regions could change. varchar(255)
cntnr_type_id Container Type Id A Foreign Key to the Container Type table. int
cntnr_id Container Id A Foreign Key to the Container table. bigint