Hostel Management System Database Design: ER Diagram, Tables and SQL

Tomas Silva is the warden of a university hostel with three blocks, and every July he sorts out who sleeps where from a spreadsheet with one row per room and three columns called student 1, student 2 and student 3. The rest of the year he chases fees, gets taps fixed and answers the university's monthly question, how full is each block. This post designs his database from a conversation: ten tables, created and loaded on SQL Server and PostgreSQL, the rule that one bed holds one student a year enforced by the engine, the five questions he actually asks answered with real output, and the three-column spreadsheet shown giving the wrong number of empty beds.

The conversation

Tomas: I run the university hostel: three blocks, Aster, Banyan and Cedar. Rooms on two floors, 101 and 102 on the ground floor, 201 upstairs. Every block starts at 101, so there is an A-101, a B-101 and a C-101.

Designer: So a room number alone does not name a room; the block and the number together do. How many students fit in a room?

Tomas: Depends on the room. Some have one bed, some two, some three. If we carry a fourth bed into a room, the room takes four. Bed 1 is by the window, then bed 2 and bed 3. Students ask for a bed, not just a room.

Designer: Then the beds are what you count. A room's capacity is the number of beds in it, so I will store the beds and count them rather than type a capacity. How long does a student keep a bed?

Tomas: One academic year. In July we allot the beds for the year, 2026-27 now. Next July it starts again: some stay in the same bed, some move, some leave. And I have to keep last year's list. Parents ask, the accounts office checks last year's dues, and the university asks me every month how full each block is.

Designer: Then an allotment is a row of its own: this student, this bed, this year. Can a bed ever have two students in the same year?

Tomas: Never. One bed, one student. And a student gets one bed, not two.

Designer: Both rules can be constraints on the allotments table, so the database refuses the second allotment instead of you catching it later. How do fees work?

Tomas: Per term, two terms a year; Term 1 is due at the end of July. Some pay in full, some pay part now and the rest later, some wait for my reminder.

Designer: So a term has an amount and a due date, and a payment is a receipt: one student, one term, some amount. What a student still owes is the term's amount minus what they paid. What else do students choose?

Tomas: The mess: veg or non-veg. They can switch at the start of any month, and the cook needs the numbers month by month. Then complaints: lights, taps, chairs, the Wi-Fi. A complaint is about a room; it is open, then someone is working on it, then it is resolved, and I want to see what is still open and how long it has waited. And every visitor signs the register at the gate: name, whom they visit, a phone number, time in and time out.

Designer: A mess plan per student per month. A complaint with a room, a status and the date it was logged, so its age is today minus that date and nobody types it. One row per visit with time out empty until they leave. And the spreadsheet with student 1, 2 and 3 is the mistake we repair at the end.

From sentences to tables

Beds, not capacity

The table that is missing a column is the point of this design. A room has no capacity; it has beds, one row each, and the capacity is however many rows there are. Carrying a fourth bed into a room is an INSERT, not a schema change.

CREATE TABLE beds (
    bed_id      integer GENERATED ALWAYS AS IDENTITY CONSTRAINT pk_beds PRIMARY KEY,
    room_id     integer       NOT NULL,
    bed_no      smallint      NOT NULL CONSTRAINT ck_beds_bed_no CHECK (bed_no > 0),
    CONSTRAINT fk_beds_room FOREIGN KEY (room_id) REFERENCES rooms (room_id),
    CONSTRAINT uq_beds_room_bed UNIQUE (room_id, bed_no)
);

And the allotment is where Tomas's two rules live. One bed, one student per year is a unique constraint on the bed and the year; one bed per student is a second one on the student and the year. The year itself is checked to look like 2026-27, with a regular expression on PostgreSQL and a LIKE pattern with character classes on SQL Server.

CREATE TABLE allotments (
    allotment_id   integer GENERATED ALWAYS AS IDENTITY CONSTRAINT pk_allotments PRIMARY KEY,
    student_id     integer       NOT NULL,
    bed_id         integer       NOT NULL,
    academic_year  char(7)       NOT NULL
        CONSTRAINT ck_allotments_year CHECK (academic_year ~ '^[0-9]{4}-[0-9]{2}$'),
    allotted_on    date          NOT NULL,
    CONSTRAINT fk_allotments_student FOREIGN KEY (student_id) REFERENCES students (student_id),
    CONSTRAINT fk_allotments_bed FOREIGN KEY (bed_id) REFERENCES beds (bed_id),
    CONSTRAINT uq_allotments_bed_year UNIQUE (bed_id, academic_year),
    CONSTRAINT uq_allotments_student_year UNIQUE (student_id, academic_year)
);

Section 3b tries to break both. A second student for bed 1 of A-101 in 2026-27, a bed that Yuki Tanaka held in 2025-26 and Elena Rossi holds now, and then a second bed for Wei Zhang:

ERROR:  duplicate key value violates unique constraint "uq_allotments_bed_year"
DETAIL:  Key (bed_id, academic_year)=(1, 2026-27) already exists.

ERROR:  duplicate key value violates unique constraint "uq_allotments_student_year"
DETAIL:  Key (student_id, academic_year)=(10, 2026-27) already exists.

SQL Server refuses the same two rows with error 2627, naming the same constraints:

Msg 2627: Violation of UNIQUE KEY constraint 'uq_allotments_bed_year'. Cannot insert duplicate key in object 'dbo.allotments'. The duplicate key value is (1, 2026-27).

Last year's row on the same bed was not in the way, because the year is part of the key. That is what "keep last year's list" turned into. The rest of the schema differs between the engines only in spelling: NVARCHAR against varchar, DECIMAL(10,2) against numeric(10,2), DATETIME2(0) against timestamp(0), and one column that had to be renamed on both: the mess plan's plan is a reserved word on SQL Server, refused with error 156, so it is plan_type.

The ER diagram

Five questions the warden asks

The year is 2026-27, the term is Term 1 and today is 16 October 2026 in every query, so every run gives the same answers. Which beds are vacant, listed by block:

 block_code |  block_name  | room_no | bed_no
------------+--------------+---------+--------
 A          | Aster Block  | 102     |      2
 A          | Aster Block  | 201     |      1
 B          | Banyan Block | 102     |      3
 C          | Cedar Block  | 101     |      1
 C          | Cedar Block  | 101     |      3
 C          | Cedar Block  | 102     |      2
 C          | Cedar Block  | 201     |      2
(7 rows)

Who lives in A-101 this year, bed by bed:

 bed_no | roll_no |  full_name  |   course    |    phone
--------+---------+-------------+-------------+--------------
      1 | S1001   | Elena Rossi | BSc Physics | +00-555-0101
      2 | S1002   | Omar Haddad | BCom        | +00-555-0102
      3 | S1003   | Arjun Mehta | BTech CSE   | +00-555-0103
(3 rows)

Who still owes for Term 1: the term's amount minus the sum of that student's receipts. Chloe Dubois paid in two receipts and is not listed, which is the whole reason a payment is a row and not a column:

 roll_no |   full_name   |   fee    |   paid   | balance
---------+---------------+----------+----------+----------
 S1005   | Kwame Mensah  | 42000.00 |        0 | 42000.00
 S1008   | Mateo Alvarez | 42000.00 |        0 | 42000.00
 S1003   | Arjun Mehta   | 42000.00 | 20000.00 | 22000.00
 S1010   | Wei Zhang     | 42000.00 | 25000.00 | 17000.00
(4 rows)

Open complaints with their age in days, oldest first, the age computed from the date logged:

 complaint_id | room  |  category  |           details           |   status    | logged_on  | age_days
--------------+-------+------------+-----------------------------+-------------+------------+----------
            6 | C-101 | electrical | Fan regulator broken        | in_progress | 2026-08-28 |       49
            2 | B-101 | plumbing   | Washroom tap leaks          | in_progress | 2026-09-20 |       26
            3 | C-102 | internet   | Wi-Fi drops every evening   | open        | 2026-10-01 |       15
            5 | A-102 | cleaning   | Room not cleaned for 3 days | open        | 2026-10-12 |        4
            8 | A-101 | furniture  | Cupboard lock jammed        | open        | 2026-10-14 |        2
(5 rows)

And the university's question, beds against allotments per block:

SELECT bl.block_code, bl.block_name,
       COUNT(b.bed_id) AS beds,
       COUNT(a.allotment_id) AS occupied,
       ROUND(100.0 * COUNT(a.allotment_id) / COUNT(b.bed_id), 1) AS occupancy_pct
FROM blocks AS bl
JOIN rooms AS r ON r.block_id = bl.block_id
JOIN beds AS b  ON b.room_id = r.room_id
LEFT JOIN allotments AS a ON a.bed_id = b.bed_id AND a.academic_year = '2026-27'
GROUP BY bl.block_code, bl.block_name
ORDER BY bl.block_code;
 block_code |  block_name  | beds | occupied | occupancy_pct
------------+--------------+------+----------+---------------
 A          | Aster Block  |    6 |        4 |          66.7
 B          | Banyan Block |    5 |        4 |          80.0
 C          | Cedar Block  |    7 |        3 |          42.9
(3 rows)

SQL Server returns the same rows for all five; it writes the age as DATEDIFF and the percentage with a CAST, and prints a zero amount as .00 where psql prints 0, which is the kind of thing worth knowing before a viva examiner asks why the two screenshots differ.

The spreadsheet, and why it counts 13 empty beds

Section 5 recreates Tomas's sheet as rooms_flat: block, room number, capacity, then student1, student2, student3, loaded with the same occupants. Finding one student means searching three columns, and a fourth bed would need a fourth column. The real damage shows when the sheet is asked how many beds are vacant, by counting empty student columns:

SELECT block_code,
       SUM(  CASE WHEN student1 IS NULL THEN 1 ELSE 0 END
           + CASE WHEN student2 IS NULL THEN 1 ELSE 0 END
           + CASE WHEN student3 IS NULL THEN 1 ELSE 0 END) AS vacant_beds
FROM rooms_flat
GROUP BY block_code
ORDER BY block_code;
 block_code | vacant_beds
------------+-------------
 A          |           5
 B          |           2
 C          |           6
(3 rows)

Thirteen vacant beds in a hostel that has 18 beds and 11 students. The extra six are the student columns of rooms that have fewer than three beds: an empty column looks like an empty bed even in a one-bed room. Counting beds that have no allotment this year gives the truth, and it agrees with the occupancy query above:

SELECT bl.block_code, COUNT(*) AS vacant_beds
FROM beds AS b
JOIN rooms AS r   ON r.room_id = b.room_id
JOIN blocks AS bl ON bl.block_id = r.block_id
WHERE NOT EXISTS (SELECT 1 FROM allotments AS a
                  WHERE a.bed_id = b.bed_id AND a.academic_year = '2026-27')
GROUP BY bl.block_code
ORDER BY bl.block_code;
 block_code | vacant_beds
------------+-------------
 A          |           2
 B          |           1
 C          |           4
(3 rows)

What this design does not do

The bed rule is per academic year, not per date: a student who leaves in January keeps the bed in this model until July, and nobody else can be allotted it. A mid-year move needs from and to dates on the allotment and an overlap rule like the hotel's in Part 7. The mess plan has only the two values the conversation gave, checked by a constraint rather than a catalogue table. And nothing demonstrates the fourth bed; the point stands as a sentence.

Frequently asked

What tables does a hostel management system database need?

Ten in this design: blocks, rooms (unique on block and room number), beds (one row per bed, so capacity is a count), students, allotments (student, bed and academic year, with two unique constraints), fee_terms, payments (one row per receipt, so part payments work), mess_plans (one row per student per month), complaints and visitors. Occupancy, balances and a complaint's age are queries.

How do you make sure one bed is allotted to one student per year?

With a unique constraint on (bed_id, academic_year) in the allotments table, and a second one on (student_id, academic_year) so a student cannot hold two beds. Both engines refuse the duplicate row with an error that names the constraint; last year's allotment of the same bed does not interfere, because the year is part of the key.

Why are student1, student2, student3 columns wrong in a hostel database?

Because they fix the number of beds in the table's shape and make an empty column look like an empty bed. In this post's sample the sheet counted 13 vacant beds in a hostel with 7; the extra six were the unused columns of one- and two-bed rooms. One row per bed and one row per allotment counts correctly and takes a fourth bed as a plain insert.

Next: Part 5, a hospital management system, the inpatient side: wards, beds again, admissions, prescriptions and the bill that adds up every line.