Table Data dictionary report - employees

Generated: 2019-01-21 8:18:42 AM
Server: PostgreSQL 9.3 (localhost:5432)
Database: datadict
Schema: public
Notes:

Table for Employees

Columns

Name Data type Not Null? Primary key? Default Comment
id integer Yes Yes nextval('employees_id_seq'::regclass) Unique identifier
employee_no character varying(6) No No Employee number
name character varying(60) No No Employee name
sin character varying(9) No No Employee Social Insurance number
birth_date date No No Date of birth
hire_date date No No Date hired
term_date date No No Date terminated
review_date date No No Review date
job_title character varying(60) No No Job title
health_number character varying(30) No No Health number
rsp_deduction_type character varying(1) No No '$'::character varying RSP deduction type; $ = Amount % = Percentage
union_number character varying(30) No No Union ID number
language_code character varying(1) No No 'E'::character varying Language code; E = English F = French
notes text No No not used
union_dues_type character varying(1) No No '$'::character varying Union dues rate type; $ = Amount % = Percentage
tax_credits numeric(15,2) No No 0 Federal Tax credit amount
other_tax_credits numeric(15,2) No No 0 Other claim amount
prov_claim_amt numeric(15,2) No No 0 Provincial claim amount
remote_allow numeric(15,2) No No 0 Remote allowance
alimony numeric(15,2) No No 0 Alimony amount
elected numeric(15,2) No No 0 Elected extra tax amount
expense_claim numeric(15,2) No No 0 Estimated expense amount for commissioned employees
union_dues numeric(15,2) No No 0 Union dues amount or rate
rsp_deduction numeric(15,2) No No 0 RSP deduction amount or rate
td1_line_6_9 numeric(15,2) No No 0 Not used
estimated_pay numeric(15,2) No No 0 Estimated pay amount for commissioned employees
tax_table character varying(2) No No 'ZZ'::character varying The province code for the tax table to use
periods smallint No No 24 The number of pay periods that employee is paid per year
last_pay_period smallint No No 0 Not used
wcb_rate numeric(5,3) No No 0 WCB (Work Safe) rate for this employee
wcb_assessable numeric(15,2) No No 0 Annual WCB (Work Safe) assessable amount
ei_rate numeric(5,3) No No 0 Employer Employment Insurance rate for this employee. Usually 1.4
retain_vacation_pay boolean No No Retain employee's vacation; TRUE = Yes FALSE = No
vacation_pay_rate numeric(5,2) No No 0 Vacation percentage rate this employee receives for vacation pay
regular_rate numeric(9,2) No No 0 Employee's regular time hourly rate
overtime_rate numeric(9,2) No No 0 Employee's overtime hourly rate
premium_rate numeric(9,2) No No 0 Employee's premium time hourly rate
salary numeric(15,2) No No 0 Employee's salary per pay period
vacation_owed numeric(15,2) No No 0 Accrued vacation amount owed
bank character varying(3) No No Employee's bank institution number for EFT
branch character varying(5) No No Employee's bank account branch (transit) for EFT
account character varying(31) No No Employee's bank account number for EFT
dept_id integer No No Employees payroll department - links to payroll_depts.id
status character varying(1) No No 'A'::character varying Employees status; A = Active T = Terminated L = On leave
gender character varying(1) No No Gender; M = Male F = Female
ytd_salary_dlrs numeric(15,2) No No 0 Year to date salary amount
ytd_advance_dlrs numeric(15,2) No No 0 Year to date advances outstanding
ytd_comm_dlrs numeric(15,2) No No 0 Year to date commission amount
ytd_regular_dlrs numeric(15,2) No No 0 Year to date regular pay amount
ytd_otime_dlrs numeric(15,2) No No 0 Year to date overtime pay amount
ytd_premium_dlrs numeric(15,2) No No 0 Year to date premium pay amount
ytd_sick_dlrs numeric(15,2) No No 0 Year to date sick pay amount
ytd_vac_dlrs numeric(15,2) No No 0 Year to date vacation paid to the employee. All pay where type = "V" and/or non retained vacation pay.
ytd_other_dlrs numeric(15,2) No No 0 Year to date other pay amount
ytd_ppip_insurable numeric(15,2) No No 0 Year to date PIPP insurable amount
ytd_ppip numeric(15,2) No No 0 Year to date PPIP amount
ytd_fed_tax numeric(15,2) No No 0 Year to date federal tax withheld amount, includes provincial except Quebec
ytd_prv_tax numeric(15,2) No No 0 Year to date provincial tax withheld amount if not included in federal tax
ytd_munic_tax numeric(15,2) No No 0 Not used
ytd_uic numeric(15,2) No No 0 Year to date Employment Insurance withheld amount
ytd_cpp numeric(15,2) No No 0 Year to date Canada Pension Plan or Quebec Pension Plan withheld amount
ytd_union numeric(15,2) No No 0 Year to date Union dues withheld amount
ytd_rsp numeric(15,2) No No 0 Year to date RSP contributions amount
ytd_wcb numeric(15,2) No No 0 Year to date WCB amount remitted
ytd_qhip numeric(15,2) No No 0 Year to date QHIP remitted
ytd_insurable numeric(15,2) No No 0 Year to date insurable amount
ytd_vac_paid numeric(15,2) No No 0 Year to date vacation pay calculated for the employee. Accrued for employees with retain_vacation_pay = True and paid to employees with retain_vacation_pay = False.
lst_salary_dlrs numeric(15,2) No No 0 Last year salary pay amount
lst_advance_dlrs numeric(15,2) No No 0 Last year advances outstanding
lst_comm_dlrs numeric(15,2) No No 0 Last year commission pay amount
lst_regular_dlrs numeric(15,2) No No 0 Last year regular pay amount
lst_otime_dlrs numeric(15,2) No No 0 Last year overtime pay amount
lst_premium_dlrs numeric(15,2) No No 0 Last year premium pay amount
lst_sick_dlrs numeric(15,2) No No 0 Last year sick pay amount
lst_vac_dlrs numeric(15,2) No No 0 Last year vacation pay amount
lst_other_dlrs numeric(15,2) No No 0 Last year other pay amount
lst_ppip_insurable numeric(15,2) No No 0 Last year PPIP insurable amount
lst_ppip numeric(15,2) No No 0 Last year PPIP amount
lst_fed_tax numeric(15,2) No No 0 Last years federal tax, includes provincial except Quebec
lst_prv_tax numeric(15,2) No No 0 Year to date provincial tax withheld amount if not included in federal tax
lst_munic_tax numeric(15,2) No No 0 Not used
lst_uic numeric(15,2) No No 0 Last year Employment Insurance withheld amount
lst_cpp numeric(15,2) No No 0 Last year Canada Pension Plan or Quebec Pension Plan withheld amount
lst_union numeric(15,2) No No 0 Last year Union dues withheld amount
lst_rsp numeric(15,2) No No 0 Last year RSP contributions amount
lst_wcb numeric(15,2) No No 0 Year to date WCB amount remitted
lst_qhip numeric(15,2) No No 0 Last year QHIP remitted
lst_insurable numeric(15,2) No No 0 Last year insurable amount
lst_vac_paid numeric(15,2) No No 0 Last year vacation pay calculated for the employee. Accrued for employees with retain_vacation_pay = True and paid to employees with retain_vacation_pay = False.
ppip_exempt boolean Yes No false Employee PPIP exempt flag; TRUE = Yes FALSE or blank = No
cpp_exempt boolean Yes No false Employee CPP exempt flag; TRUE = Yes FALSE or blank = No
image_path character varying(512) No No Not used
advance_bal numeric(15,2) No No 0 Current balance of employee advances owing
direct_deposit_method character varying(1) No No 'E'::character varying Not used
direct_deposit boolean Yes No false Employee is paid with direct deposit; TRUE = Yes FALSE = No
_dbversion integer No No Program version that last modified this record
_created timestamp without time zone No No UTC Date and time record was created
_created_by character varying(3) No No User initials that created this record
_modified timestamp without time zone No No UTC Date and time record was last modified
_modified_by character varying(3) No No User initials that modified this record
deduction_tax05_flag boolean[] No No '{f,f,f,f,f,f,f,f,f,f}'::boolean[] Not used
deduction_tax06_flag boolean[] No No '{f,f,f,f,f,f,f,f,f,f}'::boolean[] Not used
deduction_tax07_flag boolean[] No No '{f,f,f,f,f,f,f,f,f,f}'::boolean[] Not used
deduction_tax08_flag boolean[] No No '{f,f,f,f,f,f,f,f,f,f}'::boolean[] Not used
deduction_tax09_flag boolean[] No No '{f,f,f,f,f,f,f,f,f,f}'::boolean[] Not used
deduction_tax10_flag boolean[] No No '{f,f,f,f,f,f,f,f,f,f}'::boolean[] Not used
ytd_pensionable numeric(15,2) No No 0 Year to date pensionable amount
lst_pensionable numeric(15,2) No No 0 Last year pensionable amount
udf_data hstore No No ''::hstore User Defined Fields data
ytd_taxable numeric(15,2) No No 0 Year to date taxable amount
ytd_taxable_benefits numeric(15,2) No No 0 Year to date taxable benefits amount
lst_taxable numeric(15,2) No No 0 Last year taxable amount
lst_taxable_benefits numeric(15,2) No No 0 Last year taxable benefit amount
tax_exempt boolean Yes No false Employee tax exempt flag; False = No True = Yes
ei_exempt boolean Yes No false Employee Employement Insurance exmpt flag ; False = No True = Yes
ytd_benefits numeric(15,2) No No 0 Total year to date benefit amount
lst_benefits numeric(15,2) No No 0 Total last year benefit amount
ytd_deductions numeric(15,2) No No 0 Total year to date deduction amount
lst_deductions numeric(15,2) No No 0 Total last year deduction amount

Constraints

Name Type Definition Comment
employee_pkey Primary key (id)
employees_dept_code_fkey Foreign key (dept_id) REFERENCES public.payroll_depts (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION

Report generated by pgAdmin3 LTS by BigSQL