Case study · Backend application · Data modelling
EMS-Pro
An employee management system in Java 17 with a documented layered architecture — controller, service, repository — over a relational SQLite schema accessed through JDBC.
- Role
- Solo — requirements, architecture, schema and implementation
- Context
- Personal project
- Status
- In progressAutomated tests not written yet
- Stack
- Java 17
- Maven
- JDBC
- SQLite
- SQL
Overview
EMS-Pro is a console-based Java application for managing employees, departments, positions and attendance records. It was built to practise backend structure properly: requirements and design documents first, then a layered implementation over a real relational schema.
Problem & requirements
Record-keeping like this is easy to build as one tangled class. The goal was the opposite: keep business rules out of the interface, keep persistence behind repositories, and let the database enforce data integrity.
- Documented before coding: vision, requirements specification, architecture, domain model, glossary and physical database design.
- No duplicate employee emails; no negative salaries; every employee belongs to a real department and position.
My role
Solo project, from the design documents to the code.
Key features
CRUD for four entities
Employees, departments, positions and attendance records.
Lookups and reporting
Employee search by ID, attendance lookup by employee, and summary reporting.
Validation and errors
Input validation, duplicate-email prevention and custom exceptions surfaced through a console workflow.
Architecture
Each layer depends only on the one below it. Configuration, exceptions and utilities are cross-cutting packages that support the flow without taking part in it.
Controller
Entry point: receives console input and delegates. No business logic.
Service
All business rules; coordinates one or more repositories.
Repository
Repository pattern: interfaces plus JDBC implementations; talks only to the database.
JDBC · SQLite
ConnectionManager and DatabaseConfig; schema defined in database/schema.sql.
Data model
Four tables with named constraints. Integrity rules live in the schema, not only in application code, and lookups used by the app are indexed.
Departments
- DepartmentID PK
- Name
Positions
- PositionID PK
- Name
Employees
- EmployeeID PK
- Email UNIQUE
- Salary CHECK ≥ 0
- DepartmentID FK → Departments
- PositionID FK → Positions
Indexed on department and position
Attendance
- AttendanceID PK
- EmployeeID FK → Employees
- AttendanceDate
Indexed on employee and date
Engineering decisions
Layered architecture with the repository pattern
Recorded as architecture decisions in the docs: business logic only in services, persistence only in repositories.
Models without validation
Entities represent data only; validation lives in a dedicated validator used by the layers that operate on them.
Integrity enforced twice
The UNIQUE email constraint guards the data, and a DuplicateEmailException gives the user a clear message instead of a raw SQL error.
Schema in version control, database file not
schema.sql is committed; the SQLite file is generated locally and git-ignored.
Testing & reliability
No automated tests yet
JUnit 5 is configured in the Maven build, but the test directory is still empty. Automated service and repository tests are the next step.
Outcome & next steps
A working layered backend with its design documented end to end. It is still in progress.
Next steps
- Unit and integration tests for services and repositories
- Externalised database configuration
- A web or desktop presentation layer on top of the existing services