Here, I have some problem representing a hostel-room-inmate entities.

First an inmate is given a hostel seat,

Then he is allotted a particular hostel,

Then he is allotted a room.

My approach is that the inmate table contains a parent-link to the room table, the room table contains a parent-link to the hostel table.

table hostel(id, name)
table room(id, hostel_id, room_no)
table inmate(id, room_id, name, etc)

But while at the stage of allotment, the inmate is first allotted a hostel, in which the room_id is to be left blank, thereby meaning that the inmate is not allotted a hostel room and so on...

How do I solve this problem please...

I wanted to use it in Laravel development environment

(NB: I have some idea of creating many-to-many relationship type by creating relationship table, but just asking if there's better approach than this.)

  • I do not see the problem. Your scheme does not describe the process. It describes states: initial (inmate is not allotted) and final (inmate is allotted a room in a hostel). That's all. Your reasoning about displaying the intermediate state doesn't make sense.
    – Akina
    Apr 4 at 18:44
  • Sounds like you need both hostel_id and room_id on inmate - which accommodates both allocated to a hostel and allocated to a particual room (within a hostel).
    – Will A
    Apr 4 at 19:02

I would add hostel_id to inmate, and leave Room-id empty till he arrives and the room-number is chosen.

So you could also, allow inmate to book a specific room with sea view for example

And you could check if any rooms for a date are available, for example hostel 1 has 10 room and 10 users have chosen the hostel already, you ca tell him that is not possible and give additional information when a room is free again or a hostel nearby who has vacancy.

table hostel(id, name)
table inmate(id,hostel_id, room_id, name, etc)
table room(id, hostel_id, room_no)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.