Aisha Khan is the HR executive of a forty-person company, and every month she builds a sheet: one row per person, one column per day, a P, an A or an L typed into each box. At month end she counts the letters for payroll. It takes two days, and people still argue with the counts, because her supervisor writes L for late and she writes L for leave. This post designs the attendance database from a conversation with her: seven tables on SQL Server and PostgreSQL, a month of check-ins generated the same way on both, a status that is never typed because the engine works it out, and the letter grid shown giving the wrong answer to "who was on leave on the 15th".
- Download the script for your engine: p03-sqlserver.sql or p03-postgresql.sql
- Run it:
sqlcmd -S "(localdb)\MSSQLLocalDB" -I -f 65001 -i p03-sqlserver.sql, orpsql -d postgres -c "CREATE DATABASE sp_p03"thenpsql -d sp_p03 -e -f p03-postgresql.sql - Find the column that is missing from the
attendancetable: there is no status - Read Viva 1 in your output: Wei Zhang has 17 working days because he joined on the 12th
- Read Viva 2: David Chen checked in at 09:10 and is not late; the grace is inclusive
- Read section 5: the letter grid names three people on leave on the 15th, the tables name one
- Paste sections 1 and 2 into the ER diagram tool and p03-sheet.csv into the normalization checker
The conversation
Aisha: Forty employees. Each has a code like E101, a name, an email, a phone number, the date they joined, and the department they sit in: Human Resources, Accounts, Sales or Operations. Most are on the General shift, nine to six. Part of Operations works the Early shift, seven to four.
Designer: When is someone late?
Aisha: Everyone gets ten minutes' grace. Nine-ten is fine for the nine o'clock shift, nine-eleven is late. If you walk in more than two hours after your shift starts, it counts as a half-day. Ten minutes for both shifts today, but management has talked about fifteen for the Early shift in winter.
Designer: Then the grace belongs to the shift, not to the company. How do you know what time someone came in?
Aisha: The machine at the door gives me a check-in and a check-out time for each person for each day. One punch in, one punch out. Sometimes people forget to punch out. I am supposed to work out present or late from that, but today I type it, and my supervisor writes L when someone is late while I write L when someone is on leave.
Designer: Then we do not store the status at all. We store the times and the rules, and the database works out present, late, half-day or absent every time you ask. What about leave?
Aisha: Leave is a request: a type of leave from one date to another, with a reason. It stays pending until I approve or reject it, and I note who decided. Casual leave is twelve days a year, sick leave ten, earned leave fifteen, and people ask me for their balance every week.
Designer: Someone did not come in and has a pending or rejected request for that day. What is that day?
Aisha: Absent. Only approved leave explains an absence. A pending request is only a request.
Designer: Which days are not working days?
Aisha: Sunday is the weekly off for everyone; Saturday is a working day. And there is a list of company holidays, like Foundation Day on 2 October. A Sunday or a holiday inside a leave request is not leave; only working days are deducted. And Wei joined on the twelfth; he must not show as absent for the first eleven days.
Designer: So each person's working days start at their joining date, skip Sundays and holidays, and every count is made from those days. The five reports you need, the monthly summary, today's late list, absent without leave, balances and the morning headcount per department, are five queries on the same seven tables.
From sentences to tables
| Aisha said | Entity | Columns |
|---|---|---|
| the department they sit in | departments | name |
| the General shift, nine to six; ten minutes' grace; two hours makes a half-day | shifts | start_time, end_time, grace_minutes, half_day_after_minutes |
| a code like E101, a name, the date they joined | employees | employee_code, full_name, email, phone, joined_on, department_id, shift_id |
| a check-in and a check-out time for each person for each day | attendance | employee_id, work_date, check_in_time, check_out_time (NULL when forgotten); one row per person per day |
| casual twelve, sick ten, earned fifteen | leave_types | code, name, days_per_year |
| a type of leave from one date to another, pending until I decide | leave_requests | start_date, end_date, reason, status, decided_by |
| Foundation Day on 2 October | holidays | the date itself is the key, plus a name |
| present, late, half-day, absent, on leave; the balance; Sunday off | nothing | derived in every query; Sunday is a rule, not a table |
The rules are rows, the status is a query
The shift carries the numbers Aisha said out loud, so that fifteen minutes for the Early shift
in winter is an UPDATE of one row and not a change to any query:
CREATE TABLE shifts (
shift_id integer GENERATED ALWAYS AS IDENTITY CONSTRAINT pk_shifts PRIMARY KEY,
name varchar(20) NOT NULL CONSTRAINT uq_shifts_name UNIQUE,
start_time time(0) NOT NULL,
end_time time(0) NOT NULL,
grace_minutes smallint NOT NULL DEFAULT 10,
half_day_after_minutes smallint NOT NULL DEFAULT 120,
CONSTRAINT ck_shifts_times CHECK (end_time > start_time),
CONSTRAINT ck_shifts_grace CHECK (grace_minutes BETWEEN 0 AND 60),
CONSTRAINT ck_shifts_half_day CHECK (half_day_after_minutes > grace_minutes)
);And the attendance row has two times and no verdict. The unique pair is "one punch in per person per day", and the check constraint refuses a punch out before the punch in:
CREATE TABLE attendance (
attendance_id integer GENERATED ALWAYS AS IDENTITY CONSTRAINT pk_attendance PRIMARY KEY,
employee_id integer NOT NULL CONSTRAINT fk_attendance_employee REFERENCES employees (employee_id),
work_date date NOT NULL,
check_in_time time(0) NOT NULL,
check_out_time time(0) NULL,
CONSTRAINT uq_attendance_employee_day UNIQUE (employee_id, work_date),
CONSTRAINT ck_attendance_check_out CHECK (check_out_time > check_in_time)
);Both rules were tested with a second row for the same person and day and with a reversed pair of times:
ERROR: duplicate key value violates unique constraint "uq_attendance_employee_day"
DETAIL: Key (employee_id, work_date)=(1, 2026-10-15) already exists.
ERROR: new row for relation "attendance" violates check constraint "ck_attendance_check_out"
DETAIL: Failing row contains (278, 1, 2026-10-18, 18:00:00, 09:00:00).SQL Server refuses the same two rows with errors 2627 and 547, naming the same constraints. The leave request table has a third kind of rule, one that ties two columns together: a pending request has nobody who decided it, and a decided one must say who:
CONSTRAINT ck_leave_requests_decision CHECK ((status = 'pending' AND decided_by IS NULL)
OR (status <> 'pending' AND decided_by IS NOT NULL))The status itself lives in the queries. Today's late list compares the check-in with the shift start plus the grace, and decides late against half-day with the second threshold, all read from the shift row:
SELECT e.employee_code,
e.full_name,
s.name AS shift,
s.start_time,
a.check_in_time,
(EXTRACT(EPOCH FROM a.check_in_time - s.start_time) / 60)::int AS minutes_late,
CASE WHEN a.check_in_time <= s.start_time + make_interval(mins => s.half_day_after_minutes)
THEN 'late' ELSE 'half-day' END AS status
FROM attendance a
JOIN employees e ON e.employee_id = a.employee_id
JOIN shifts s ON s.shift_id = e.shift_id
WHERE a.work_date = DATE '2026-10-15'
AND a.check_in_time > s.start_time + make_interval(mins => s.grace_minutes)
ORDER BY minutes_late DESC;
employee_code | full_name | shift | start_time | check_in_time | minutes_late | status
---------------+--------------+---------+------------+---------------+--------------+----------
E111 | Chloe Dubois | General | 09:00:00 | 11:20:00 | 140 | half-day
E102 | Maria Garcia | General | 09:00:00 | 09:47:00 | 47 | late
E107 | Kwame Mensah | Early | 07:00:00 | 07:25:00 | 25 | late
E105 | Omar Haddad | General | 09:00:00 | 09:12:00 | 12 | late
(4 rows)
David Chen checked in at 09:10 that day and is not in the list: the grace boundary is inclusive,
as Aisha said. On SQL Server the same query uses DATEADD for the thresholds and
DATEDIFF for the minutes, and returns the same four rows.
The ER diagram
Five reports, one month of data
The sample has twelve employees and 276 attendance rows for October 2026, generated by a
recursive CTE on SQL Server and by generate_series on PostgreSQL from the same formula,
so both engines hold the same month. October 2026 has 25 working days: 31 minus four Sundays
minus two holidays. The payroll summary, with the status derived per day:
employee_code | full_name | working_days | present | late | half_day | on_leave | absent
---------------+---------------+--------------+---------+------+----------+----------+--------
E101 | Aisha Khan | 25 | 25 | 0 | 0 | 0 | 0
E102 | Maria Garcia | 25 | 23 | 1 | 1 | 0 | 0
E103 | David Chen | 25 | 24 | 1 | 0 | 0 | 0
E104 | Priya Nair | 25 | 19 | 1 | 0 | 5 | 0
E105 | Omar Haddad | 25 | 18 | 6 | 1 | 0 | 0
E106 | Elena Rossi | 25 | 20 | 0 | 0 | 4 | 1
E107 | Kwame Mensah | 25 | 21 | 2 | 0 | 0 | 2
E108 | Hannah Weber | 25 | 24 | 0 | 0 | 0 | 1
E109 | Arjun Mehta | 25 | 23 | 0 | 0 | 0 | 2
E110 | Sofia Petrova | 25 | 24 | 0 | 0 | 1 | 0
E111 | Chloe Dubois | 25 | 24 | 0 | 1 | 0 | 0
E112 | Wei Zhang | 17 | 16 | 1 | 0 | 0 | 0
(12 rows)Wei Zhang's 17 working days start at his joining date. Absent without approved leave on the 15th, where the request's status is shown so that Aisha can see why each absence is unexcused:
employee_code | full_name | department | leave_request
---------------+--------------+------------+---------------
E106 | Elena Rossi | Sales | no request
E108 | Hannah Weber | Operations | pending
E109 | Arjun Mehta | Operations | rejected
(3 rows)Priya Nair was away the same day and is not listed, because her earned leave from the 12th to the 16th is approved. Her balance shows the other rule from the conversation, that only working days count: Elena Rossi's request from 23 to 27 October is five calendar days and four leave days, because the 25th is a Sunday, and David Chen's pending December request spans a holiday and a Sunday and counts three.
employee_code | full_name | allowance | taken | pending | balance
---------------+---------------+-----------+-------+---------+---------
E101 | Aisha Khan | 15 | 0 | 0 | 15
E102 | Maria Garcia | 15 | 0 | 0 | 15
E103 | David Chen | 15 | 0 | 3 | 15
E104 | Priya Nair | 15 | 8 | 0 | 7
E105 | Omar Haddad | 15 | 5 | 0 | 10
E106 | Elena Rossi | 15 | 4 | 0 | 11
E107 | Kwame Mensah | 15 | 0 | 0 | 15
E108 | Hannah Weber | 15 | 0 | 0 | 15
E109 | Arjun Mehta | 15 | 0 | 0 | 15
E110 | Sofia Petrova | 15 | 0 | 0 | 15
E111 | Chloe Dubois | 15 | 0 | 0 | 15
E112 | Wei Zhang | 15 | 0 | 0 | 15
(12 rows)And the morning headcount per department on the 15th, present meaning checked in, on time or not:
department | headcount | present | on_leave | absent
-----------------+-----------+---------+----------+--------
Accounts | 3 | 3 | 0 | 0
Human Resources | 2 | 2 | 0 | 0
Operations | 4 | 2 | 0 | 2
Sales | 3 | 1 | 1 | 1
(4 rows)The letter grid, and what it cannot say
Section 5 builds Aisha's sheet as a table, attendance_october, with columns
d01 to d31 and the letters typed in, including two slips of the kind she
described: Omar Haddad's 15th is L because the supervisor meant late, and Hannah Weber's 15th is L
although her sick-leave request is still pending. Who was on leave on the 15th:
employee_name | department | d15
---------------+------------+-----
Hannah Weber | Operations | L
Omar Haddad | Sales | L
Priya Nair | Sales | L
(3 rows)
One of the three was on leave. The monthly count from the grid needs all 31 columns named in a
VALUES list, and it cannot count lateness at all, because a late day is a P:
employee_name | p_days | a_days | l_days
---------------+--------+--------+--------
Aisha Khan | 25 | 0 | 0
Arjun Mehta | 23 | 2 | 0
Chloe Dubois | 25 | 0 | 0
David Chen | 25 | 0 | 0
Elena Rossi | 20 | 1 | 4
Hannah Weber | 24 | 0 | 1
Kwame Mensah | 23 | 2 | 0
Maria Garcia | 25 | 0 | 0
Omar Haddad | 24 | 0 | 1
Priya Nair | 20 | 0 | 5
Sofia Petrova | 24 | 0 | 1
Wei Zhang | 17 | 0 | 0
(12 rows)Against the payroll summary above, Omar Haddad's six late arrivals and his half-day are invisible, and his one "leave" day was a late one. The repaired query asks the tables for everyone's status on the 15th and gets five different answers from two times and three rules:
full_name | shift | check_in_time | status
---------------+---------+---------------+----------
Aisha Khan | General | 09:01:00 | present
Arjun Mehta | General | | absent
Chloe Dubois | General | 11:20:00 | half-day
David Chen | General | 09:10:00 | present
Elena Rossi | General | | absent
Hannah Weber | Early | | absent
Kwame Mensah | Early | 07:25:00 | late
Maria Garcia | General | 09:47:00 | late
Omar Haddad | General | 09:12:00 | late
Priya Nair | General | | on leave
Sofia Petrova | General | 08:59:00 | present
Wei Zhang | Early | 07:00:00 | present
(12 rows)
What this design does not do
Half-day is decided from the check-in alone; someone who arrives on time and leaves at noon counts present, because Aisha's rule only mentioned the morning. Nothing stops two leave requests from overlapping, and if two approved ones covered the same day the balance would count it twice; the sample has none. And the forgotten punch-out is recorded as a NULL and left there, which is honest and is also where a "fix the punch" workflow would start.
The three habits this project teaches: store times and rules, never verdicts; put the rule's numbers in a row so they can change; and when a sheet has one column per day, that is a table with a date column trying to get out.
Frequently asked
- What tables does an attendance management system database need?
- Seven in this design: departments, shifts (with the start time, the grace minutes and the half-day threshold as columns), employees (with a joining date), attendance (one row per employee per day, with a check-in and an optional check-out time), leave_types, leave_requests (a date range, a status and who decided) and holidays. Present, late, half-day, absent and on leave are computed in the queries, never stored.
- How do you calculate late and half-day from check-in time in SQL?
- Compare the check-in with the shift's start plus the grace: on PostgreSQL check_in_time <= start_time + make_interval(mins => grace_minutes) is present, up to the half-day threshold is late, later is half-day; on SQL Server the same with DATEADD. The thresholds come from the shift row, so a change for one shift is an UPDATE. In this post's run a 09:10 check-in on a 09:00 shift counted present and 09:12 counted late.
- Why is a spreadsheet with one column per day a bad attendance database?
- Because every question needs all 31 columns named, the letters are typed by people and disagree (L meant late to one person and leave to another), and a late arrival is just a P. In this post's data the grid listed three people on leave on the 15th when one was, and could not count lateness at all. One row per employee per date with the actual times answers every report correctly.
Next: Part 4, a hostel management system: blocks, rooms and beds, one student per bed per year, fees paid in parts, and the sheet with student 1, 2 and 3.