Overview
A relational database system for managing bookings across multiple hotels, designed from a conceptual ER model through to a working PostgreSQL schema. It models hotels, rooms with per-room bed configurations, customers, price plans, multi-room bookings, payments, and hotel-specific extra services, with the schema itself, not just application code, responsible for keeping booking totals, room counts, and booking status correct as data changes.
Architecture
The schema is organized around 13 tables in third normal form, connected through primary and composite foreign-key relationships, for example Room is keyed by (hotel_id, number) and referenced compositely by Beds_in_room and Booking_item. Business logic that needs to run no matter which client touches the data, total price calculation, booking-status transitions, and service-usage validation, is implemented as PL/pgSQL functions and triggers rather than left to application code, so the database itself enforces consistency.
Key Features
- Multi-hotel, multi-room bookings with per-room bed-type configuration
- Automatic booking total-price recalculation via triggers whenever booking items or extra services are inserted, updated, or deleted
- Automatic booking-status transitions driven by payment events
- CHECK-constraint-enforced business rules: positive prices, valid check-in/check-out ranges, adult-only customers, 1-5 star ratings
- Flexible price plans and hotel-specific extra services with optional per-booking usage limits
- Payment tracking across multiple methods and statuses (pending, succeed, failed, refunded, under review)
- 20+ SQL queries covering joins, aggregations and subqueries
Technical Challenges
- Keeping a booking's total price correct automatically as its rooms and extra services change, without relying on the application layer to recompute it
- Modeling composite relationships correctly, since rooms are identified by hotel plus room number rather than a single surrogate key, across every dependent table
- Writing set-based queries, such as customers with confirmed bookings or bookings using every extra service a hotel offers, using correlated subqueries and set operators instead of procedural loops
Engineering Decisions
- Pushed price and status consistency into PL/pgSQL functions and triggers instead of application code, so the guarantee holds no matter what inserts or updates the data
- Used composite foreign keys (hotel_id, room_number) rather than a synthetic room ID, since a room only makes sense in the context of its hotel
- Modeled extra services and their usage limits per hotel rather than globally, since two hotels can define the same service differently
Technologies Used
PostgreSQL as the database engine, with the schema, constraints, and business logic written directly in SQL and PL/pgSQL rather than an ORM, to practice relational modeling and set-based query writing directly.
Lessons Learned
- Deepened understanding of relational modeling and normalization beyond textbook examples, including when composite keys are the right choice
- Learned to express business rules as constraints and triggers so the schema stays correct independent of the application
- Practiced writing SQL well beyond basic CRUD: correlated subqueries, EXISTS/NOT EXISTS, and set operators like EXCEPT
