PD/PM Database Table Structure

1. PD/PM Database Table Structure A presentation explaining the linkage between the tables with the employee databases.

2. Company Pay Periods to Holiday & Absence and Pay History Company Pay Periods Holiday & Absence Pay History

3. The Company Pay Periods is used to set defaults for all employees within the system. The Company Pay Periods determine the default hours per day which is used when calculating Hours Lost and Days Lost on the Holiday and Absence Screen. The Company Pay Periods affect the calculations for the Annual Pay and Hourly Rate on the Pay History Screen. The Annual Pay is calculated as follows, determined by the Pay Basis. Pay Basis Formula Per Annum Annual Pay Per Month Monthly Pay x 12 Per Week Weekly Pay x Company Weeks Per Year Per Day Daily Pay x Company Working Days Per Year Per Hour Hourly Rate x Contracted Hours Per Week x Company Weeks By Year Per Shift Pay Amount Per Shift x Shifts Per Year The Hourly Rate is calculated as follows, determined by the Pay Basis. Pay Basis Formula Per Annum (Annual Pay / Company Days Per Year) / (Contractual Hours Per Week / 5) Per Month (Monthly Pay x 12 / Company Weeks Per Year) / Contracted Hours Per Week Per Week Weekly Pay / Contracted Pay Per Week Per Day Pay Per Day / Hours Per Day Per Shift Shift Pay / Hours Per Shift Per Hour Pay Per Hour Company Pay Periods to Holiday & Absence and Pay History

4. Company Details to Employee Details and User Details Company Details Employee Details User Details

5. The Company Details are used to set parameters for each Company Office Location including Statutory Holidays. The Company Details is linked to the Employee Details by the Office field. This field is determined by a system option which in turn dictates which Statutory Holidays each Employee has. By default the employ takes the statutory holiday from the Head Office The Company Details is linked to the User Details by the Office which determines which Office address appears on Mail-merged letters by that User. The Employee Details is Linked to the User Details by a User selecting his/her record. This is used for Mail merges and when logging into the Employee Self Service module (PD only). Company Details to Employee Details and User Details

6. Current Pay to Employee Details and Pay History Current Pay Employee Details Pay History

7. The Current Pay contains the actual current pay details, immediately previous pay details and the difference between the two. The Current Pay is imbedded into the Employee Details screen on the pay details tab and are displayed as calculated fields. The Current Pay is linked to the Pay History essentially by date as it displays the most recent past record. Any Pay History records that are set to a future date will not become the Current Pay until that date is reached and a Recalculate is run. The Current Pay includes all the fields from the Pay History and also the Previous record to the current one. It also contains details of the difference in both value and percentage. Any fields that are added to the Pay History Screen are NOT added to the Current Pay details. If a report/query is to include any new fields then both the Pay History table and the Current Pay table must be include and linked by Staff No. AND From date. Current Pay to Employee Details and Pay History

8. Emergency Contacts Employee Group of screens to Employee Details and Company Vehicles Employee Details Company Vehicles Vehicle Usage Employee Photos Employee Notes Correspondence History Employee Images

9. The Company Vehicles is connected to the Employee Details screen by the Vehicle Reg. Pick-list. The pick-list is a list of company Cars. This List CANNOT be added to via the Employee Details screen. The Company Vehicles is connected to the Vehicle Usage screen by the Vehicle Reg Pick-list. This is used to choose which car has been used. It is geared around a pool car system but can be used as a Full Company Car scenario. The rest of the Screens in The Employee group are linked to the Employee Details screen by the Employee Number. The Employee Photos screen can only hold one photo per employee though. The correspondence History screen is a self populating screen which holds info on letters that were generated from templates in the system. Employee Group of screens to Employee Details and Company Vehicles

10. Employment Group of screens to Employee Details Career History Employee Details Appraisal History Accident History Medical History Appraisal Assessment Appraisal Training Agreed Discipline & Grievance Appraisal Key Objectives Agreed Exit Interviews Employment History

11. The Career History is Linked to the Employee Details screen via the Career Events. Fields on the screens can be set up to create events which are recorded on the Career History screen. Only the default fields are linked so that if, for example, an employee changes Department then it will create a record on the Career History screen and will display the new value for the Department (subject to Department being set-up as a Career event). New fields that are added to the Employee details screen CANNOT be linked to the Career History screen and if a field label changes then it should be changed on the Career History screen as-well. The Appraisal History screen is linked to the Employee Details screen by Employee number. The Appraisal Assessment, Appraisal Key Objectives agreed and the Appraisal Training Agreed and linked to the Appraisal History screen as imbedded tabs. These are linked to the main screen by the appraisal number field which is a hidden field. The appraisal number field is the same as the Pkey field on the Appraisal History table. The rest of the Screens in The Employment group are linked to the Employee Details screen by the Employee Number. Employment Group of screens to Employee Details

12. Absence & Payroll Group of screens to Employee Details Employee Details Current Pay Absence Allowance Pay History Holiday & Absence Maternity Benefits Project Time Sheets Bank Details

13. The Pay History screen and Current Pay details are as mentioned earlier The Absence Allowance is connected to the Employee Details screen by Employee No. as is the Holiday and Absence screen.The Holiday and Absence screen is then linked to the Absence allowance screen essentially by the category of absence. If an employee takes a day off that is of the Holiday category then that day will be deducted from their Holiday Categorized Absence Allowance. If the employee does not have an absence allowance of the relevant category then a message will appear to confirm that and ask if you still want to create that day. The Absence Allowance rules are created through the Allowance wizard and are then allocated to the relevant employees. The rest of the Screens in Absence & Payroll group are linked to the Employee Details screen by Employee no. Absence & Payroll Group of screens to Employee Details

14. Training & Education Group of screens to Employee Details Employee Details Training History Education History CPE/CPD Skills Professional Memberships Qualifications

15. The CPE/CPD screen and the Training History screen are linked to the Employee Details screen by Employee No. The CPE/CPD screen is linked to the Training History screen by Year. The year that the course started affect the CPE/CPD points for that year. This only applies if people are earning points from their relevant courses. The rest of the screens in Training & Education group are linked to the Employee Details screen by Employee no. Training & Education Group of screens to Employee Details

16. This leads us on to a Diagram that looks like this….. The Whole Batch of screens

17. Emergency Contacts Correspondence History Employee Images Company Vehicles Vehicle Usage Company Pay Periods Pay History Employee Notes Employee Photos Company Details Current Pay Holiday & Absence Training History CPE/CPD Absence Allowance Employee Details Professional Memberships Project Time Sheets Qualifications Bank Details Skills Benefits Education History Maternity Appraisal History Employment History Disciplinary & Grievance Accident History Career History Appraisal Key Objectives Agreed Appraisal Assessment Appraisal Training Agreed Exit Interviews Medical History