1. Introduction

This project involves the design and implementation of a relational database system to manage employee payroll, allowances, and contact information. The goal was to move from a manual, paper-based or flat-file system to a centralized SQL database that automates salary calculations and ensures data integrity.

2. Tools I Used

• SQL: The backbone of my project, allowing me to create database and make adjustments to optimize workflow.

• PostgreSQL: The chosen database management system.

• Draw.io: draw.io for ER and Schema Diagrams.

• Visual Studio Code: My go-to for database management and executing SQL queries.

• Git & GitHub: Essential for version control and sharing my SQL scripts, ensuring collaboration and project tracking.

3. Database Setting Up

3.1 Database Architecture

I organized the database into four main areas to keep things clean and easy to manage. Instead of one giant, messy spreadsheet, each table has a specific job:

• Employee Information: Stores basic details like names, job titles, and base salaries.

• Contact Details: A separate list for phone numbers, which makes it easy to add multiple numbers for one person without cluttering the main list.

• Allowances: Automatically calculates monthly perks like meal and transport money.

• Monthly Payroll: This is where everything comes together, it pulls info from the other tables to figure out exactly how much someone should be paid each month.

ER Diagram

Schema Diagram

3.2 Setting Up the Tables (SQL)

This is the technical code I wrote to build the structure described above:

create table employee (
    employee_id serial primary key,
    full_name varchar(225),
    gender varchar(20) check (gender in ('Male', 'Female', 'Non-binary', 'Other')),
    date_of_birth date,
    date_of_join date not null,
    job_title varchar(225),
    department varchar(225),
    email varchar(225) unique,
    fixed_salary decimal(10,2) not null
);

create table phone (
    employee_id int references employee(employee_id) not null,
    phone_num varchar(20) not null,
    primary key (employee_id, phone_num)
);

create table allowance (
    allowance_id serial primary key,
    employee_id int references employee(employee_id) not null,
    pay_period date not null, 
    meal decimal(10,2) default 0, 
    transport decimal(10,2) default 0,
    total_allowance decimal(10,2) generated always as (meal + transport) stored
);


create table payroll (
    pay_id serial primary key,
    employee_id int references employee(employee_id) not null,
    allowance_id int references allowance(allowance_id),
    pay_period date not null,
    working_day decimal(4,1) not null,
    unpaid_leave decimal(4,1) not null,
    total_pay_day decimal(4,1) generated always as (working_day - unpaid_leave) stored,
    total_salary decimal (10,2),
    gross_salary decimal (10,2),
    total_deduction decimal (10,2),
    net_salary decimal (10,2)
);

update payroll
set 
    total_salary = sub.cal_salary,
    gross_salary = sub.cal_gross,
    total_deduction = sub.cal_gross * sub.deduction_rate,
    net_salary = sub.cal_gross * (1-sub.deduction_rate)
from (
    select
        payroll.pay_id,
        payroll.total_pay_day * employee.fixed_salary/52/40 as cal_salary,
        payroll.total_pay_day * employee.fixed_salary/52/40 + coalesce(allowance.total_allowance,0) as cal_gross,
        case
            when employee.fixed_salary <= 70000 then 0.25
                when employee.fixed_salary <= 95000 then 0.3
                else 0.4
            end as deduction_rate
    from payroll
        left join employee on payroll.employee_id = employee.employee_id
        left join allowance on payroll.allowance_id = allowance.allowance_id
    ) as sub
where payroll.pay_id = sub.pay_id and
    payroll.net_salary is null and
    payroll.pay_period = '2026-03-01'; -- pay_period is updated every period when running this query

To view the full technical scripts, click here

3.3 How the Automation Works:

I designed this system to do the "heavy lifting" automatically so that manual work is minimized:

• Connections: When a new person starts, the system gives them a unique ID. This ID acts like a "link" that connects their records across all tables automatically.

• Automatic Totals: I set up the tables to do their own math. For example, when you enter meal and transport costs, the "Total Allowance" updates itself instantly.

• Tax Rules: The system is programmed to automatically apply the right deduction rates based on different pay brackets.

• Handling Time Off: The system calculates a daily rate for each employee and automatically subtracts the correct amount if they have unpaid leave, ensuring the final "Take-home pay" is always accurate.

4. What I Learned

• Relational Database Design & Normalization: I mastered the ability to transition from flat-file data to a 3NF (Third Normal Form) relational schema by separating multi-valued attributes into their own entities (e.g., creating a dedicated phone table) to eliminate redundancy.

• Advanced Automation with Generated Columns: I became proficient in using GENERATED ALWAYS AS ... STORED to automate business calculations, such as summing total allowances and calculating total pay days directly within the table architecture.

• Data Integrity & Cleaning: I learned how to handle NULL values by using COALESCE during math operations and applying ALTER TABLE constraints to ensure data consistency after ingestion.

• Complex Multi-Table Updates: I developed advanced UPDATE ... FROM queries that use joins and CASE statements to apply conditional business logic (tax deduction rates) across different payroll periods.