Table: attendance
Column Type Description
Unique identifier for each
at_id int(11) attendance
emp_id int(20) Foreign key to employee table
log_type tinyint(1) Log type (1-4)
datetime_log datetime Log date and time
date_updated datetime Date and time of update
Table: deductions
Column Type Description
Ded_id int(30) Primary key
name text Deduction name
description text Deduction description
Table: department
Column Type Description
Dept_id int(30) Primary key
name text Department name
Table: employee
Column Type Description
Emp_id int(20) Unique identifier for
employee_no varchar(100) Employee number
firstname varchar(50) Employee first name
middlename varchar(20) Employee middle name
lastname varchar(50) Employee last name
Dept_id int(30) Foreign key to department
pos_id int(30) Foreign key to position table
salary double Employee salary
table: employee_allowances
Column Type Description
allow_id int(30) Primary key
Emp_id int(30) Foreign key to employee table
EA_id int(30) Foreign key to allowances table
type tinyint(1) Allowance type (1-3)
amount float Allowance amount
date_created datetime Date and time of creation
Table: employee_deductions
Column Type Description
Ed_id int(30) Primary key
Emp_id int(30) Foreign key to employee table
ded_id int(30) Foreign key to deductions table
type tinyint(1) Deduction type (1-3)
amount float Deduction amount
date_created datetime Date and time of creation
Table: payroll
Column Type Description
Pay_id int(30) Primary key
ref_no text Reference number .
date_from date The start date of the payroll period
date_to date The end date of the payroll period
Indicates the status of the payroll record
status tinyint(1) (new or computed)
Indicates the frequency of the payroll
type Tinyint(1) period (monthly or semi-monthly)
The date and time when the payroll
date_created datetime record was created.
Table: allowances
Column Type Description
id int(30) Primary key
allowance text Allowance name
description text Allowance description
USER TABLE
Column Type Description
User_id int(11) Unique identifier for each
Column Type Description
attendance
User_name int(20) Foreign key to employee table
Type of user acoount:1
For admin,2
type tinyint(1) For staff
name VARCHAR(100) Full name of the user
address text Address of the user
contact text Contact information for the user
passsword VARCHAR(200) Password for the user account
Table:-Position
Column Type Discription
NOT NULL, PRIMARY KEY,
pos_id int(30) AUTO_INCREMENT
name text Name of position
dep_id int(30) Foreign key
Table:-payroll item
USER TABLE
Column Type Description
Unique identifier for each
User_id int(11) attendance
User_name int(20) Foreign key to employee table
Unique identifier for the payroll item
pay_item_id Int(30) record
Pay_id Int(30) Foreign key
Emp_id Int(30) Foreign key
Numbur of days the employee was
present Int(30) present
Numbur of days the employee was
absent Int(10) absent
Numbur of days the employee was
late text late
salery double Employee salary
Total amount of allowance received
allow_amount double by the employee
Total amount of deduction made
ded_amount double from the employee salary
Details of the deduction made from
deduction text employee salary
Details of allowance received by the
allowances text employee for payroll period
Total amount of deductions made
net Int(11) from employee salary
Date and time the payroll item was
date_created date-time created
