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