Footprints Data Model

Table
Name schd_actual_period
Comment Given the definitions of the standard work periods and holidays, these are the actual 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. In FootPrints Perl, this table is generated by the Perl calendar code, so that the dashboard can use the information in the calculation of work hours elapsed. The only purpose of this table is to make it possible to calculate the work hour time difference between two dates. A program that can understand the Calendar Service API schedule, and any versioning of it, will compute the actual work periods and save them to this table. Then a program in the Java layer can compute the work hour difference between two dates, just as well as a program in the database layer.

Column
Name Datatype Comment Is PK Is FK
schd_actual_period_id bigint The automatically generated primary key of the schd_actual_period table. Yes No
begin_date datetime Begin Date and Time of the work hour period. No No
end_date datetime End Date and Time of the work hour period. No No
work_durn_in_mins int 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. No No
schd_id bigint A Foreign Key to the Calendar that was used to generate the actual periods. No Yes
cum_work_durn_in_mins int 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. No No
begin_date_utc_s varchar(23) 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. No No
end_date_utc_s varchar(23) 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. No No