Heierling GmbH makes and sells made-to-measure ski boots in Davos. I worked there as a sales associate, and that is where the case came from: I knew first-hand how stock moved through the shop.
The system itself, however, is purely academic work. I designed it from scratch for the Information Engineering module; it was not commissioned by the company and has never been in use there — they still run their own internal systems.
What makes the case distinctive is its three stock locations — the outside warehouse, the one pair of each model and size kept in the shop for customers to try on, and the area holding boots reserved for specific customers. The system has to work out where each pair is rather than simply counting units, and flag a restock when only the display pair is left.
The goal was to implement a complete relational database with referential integrity, role-based access and automation of business operations through functions and triggers.
01Requirements analysis and E/R modelling
Identifying entities (Product, Customer, Order, Supplier, Employee), attributes and relationships. Designing the Entity-Relationship diagram with cardinalities and integrity constraints.
02SQL schema implementation
Translating the E/R model into CREATE TABLE statements in PostgreSQL with primary keys, foreign keys, CHECK constraints, default values and appropriate data types for each entity.
03PL/pgSQL functions
Developing stored functions for all business operations: order registration, stock updates, total calculation, product search by category and inventory report generation.
04Triggers and automation
Implementing AFTER INSERT and AFTER UPDATE triggers to update stock automatically when orders are processed and to raise alerts when inventory falls below the minimum threshold.
05Role-based access control
Creating database roles (admin, staff, readonly) with granular permissions via GRANT and REVOKE. Each role reaches only the tables and operations that correspond to it.
06Docker environment
Deploying PostgreSQL in a Docker container on Debian Linux for environment reproducibility. The repository includes instructions to bring the database up on any machine with a single command.
This project was my first complete implementation of a production relational database. The difference between writing basic SQL and designing a system with real referential integrity, triggers and roles is substantial — I learned that most of the work sits in the modelling beforehand, not in the SQL code itself.
It was also my first experience with Docker as a tool for environment reproducibility, a pattern I now use in all my projects.