Subject Area : 140 - Workspace Calendar and Operation Schedule
Subject Area(s) of Model "Renoir Domain Model Detail"
# Name Definition ER Diagram Entity/Table
Total
. 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. Details.. Details..
ER Diagram(s) of Subject Area "140 - Workspace Calendar and Operation Schedule"
# Name Physical Display Level
Total 2
. Logical Column
. Physical Column
Entity/Table(s) of Subject Area "140 - Workspace Calendar and Operation Schedule"
# Physical Name Name Logical Only Do Not Generate Definition Attribute/Column
Total 9
. 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. Details..
. 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. Details..
. 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. Details..
. cal_type Calendar Type false The type of Calendar, Workspace or Personal Details..
. 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. Details..
. 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. Details..
. 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. Details..
. 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. Details..
. 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.

Details..
Attribute/Column(s) of Entity/Table "Agent"
# Physical Name Name Definition Physical Data Type
Total 2
. 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
Attribute/Column(s) of Entity/Table "Calendar Item"
# Physical Name Name Definition Physical Data Type
Total 24
. 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
Attribute/Column(s) of Entity/Table "Calendar Schedule"
# Physical Name Name Definition Physical Data Type
Total 3
. 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
Attribute/Column(s) of Entity/Table "Calendar Type"
# Physical Name Name Definition Physical Data Type
Total 2
. 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
Attribute/Column(s) of Entity/Table "Schedule"
# Physical Name Name Definition Physical Data Type
Total 7
. 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
Attribute/Column(s) of Entity/Table "Schedule Actual Period"
# Physical Name Name Definition Physical Data Type
Total 8
. 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)
Attribute/Column(s) of Entity/Table "Time Zone Local Hour"
# Physical Name Name Definition Physical Data Type
Total 12
. 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
Attribute/Column(s) of Entity/Table "Time Zone Region"
# Physical Name Name Definition Physical Data Type
Total 3
. 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
Attribute/Column(s) of Entity/Table "Workspace Container"
# Physical Name Name Definition Physical Data Type
Total 3
. 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