Member Avatar for ahmedsa

Hi guys i want to make database for payroll only but i dont know how to make structure for this
suppose i want to calculate payroll every month for employees as following
basic salary
bonus
overtime
absence
deduction
meaning
salary = basic salary +bonus+overtime-absence-deduction
How i make database (ERD)Diagram for this
Meaning tables i need and relations

I work with sql server 2005 and visual studio 2005

  • 3 Contributors
  • 3 Replies
  • 289 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by ahmedsa

Dani AI

Generated

— a practical ERD pattern is to treat payroll as periodic “runs” made up of many small, typed line items (earnings and deductions) instead of one wide table with many columns. That keeps the design flexible (new bonus types, penalties or tax lines don’t require schema changes), makes auditing easy, and simplifies month-to-month comparisons. ’s hunch about this being an exercise is fair, and ’s pointer toward existing data-model examples is a useful starting place for reference designs.

Suggested core tables and their roles (conceptual):

  • Employees — immutable master data for each worker.
  • PayPeriods — one row per payroll period (start/end).
  • PayrollRuns — one row per employee per PayPeriod (snapshot metadata, run status).
  • PayrollLines — one row per earning or deduction (type code, amount, qty, source reference).
  • Attendance / TimeEntries — raw hours, absences; linked to PayPeriod and Employee.
  • Lookup tables — EarningType, DeductionType, TaxCode, RateSchedules.

Key relationships: Employees 1.. PayrollRuns; PayrollRuns 1.. PayrollLines; Attendance links to Employee and PayPeriod (and PayrollLines may reference Attendance rows). Keep monetary values as DECIMAL(18,2) and store a snapshot of any rate/tax used on the PayrollLine so historical recalculation is reliable.

Example aggregation (SQL Server 2005 compatible) to produce totals for a run:

SELECT p.EmployeeID, p.PayrollID,
  SUM(CASE WHEN pl.LineType = 'EARNING' THEN pl.Amount ELSE 0 END) AS TotalEarnings,
  SUM(CASE WHEN pl.LineType = 'DEDUCTION' THEN pl.Amount ELSE 0 END) AS TotalDeductions,
  SUM(CASE WHEN pl.LineType = 'EARNING' THEN pl.Amount ELSE 0 END)
    - SUM(CASE WHEN pl.LineType = 'DEDUCTION' THEN pl.Amount ELSE 0 END) AS NetPay
FROM PayrollRuns p
JOIN PayrollLines pl ON pl.PayrollID = p.PayrollID
WHERE p.PayPeriodID = @PayPeriodID
GROUP BY p.EmployeeID, p.PayrollID;

Practical notes: generate PayrollLines from Attendance/time/overtime via a controlled process (stored procedure), keep line items immutable for audit, index foreign keys, and store reason/comments for any manual adjustments. Avoid storing computed totals except as snapshots for audits; recompute from lines for validations. Account for rounding rules and local tax/regulatory complexity in application logic rather than ad-hoc columns.

Recommended Answers

All 3 Replies

Member Avatar for Reverend Jim

I have a feeling that this is a homework assignment.

Member Avatar for ahmedsa

ok thank you for reply i create four entries(tables)
1- employee(salary)
2- attendance
3- addition(bonus +overtime)
4-deductions(absence+punishment)
1-what i need is what relation between attendance
and deduction
2- payroll table that store (deduction and addition ) what the relation between payroll table and deduction table and addition table that store all these information

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.