SELF Join Table Relation

database-design

I have hotel booking system, Where there are Partitions rooms too and they can be merge into one.

Scenario : 3 rooms (A,B and C) can be merge to Make 1 Big (ABC) room.
Validation : When any one of A, b or c is booked, we cannot booked ABC. Similarly, when ABC is booked, we cannot Booked A, B and C.
So, What I have done, is create a new column parent in the table, add new room named ABC and set ABC identity id in column parent of room A, B and C.

What kind of relation would be useful, One to One self reference, or something else?

Best Answer

I would probably add a boolean column to the rooms table indicating "virtual rooms" (room "ABC", for example, would have this set). Then add another table called VirtualRoomMembers, with two columns, both of which are foreign keys back to your Rooms table's primary key (assuming room_id int here):

CREATE TABLE VirtualRoomMembers (
    room_id int NOT NULL,
    sub_room_id int NOT NULL
)

A many-to-many design would account for the possibility that you could have multiple usable groupings for a set of rooms; perhaps you can book ABC, AB, or BC.

Now you insert records in that table to define which rooms make up which virtual rooms, and you can easily take these into account when anti-joining to the reservations to see what's available:

SELECT
    r.room_id FROM Rooms r
    LEFT OUTER JOIN VirtualRoomMembers vrm --Get the sub-rooms
        ON r.room_id = vrm.room_id
    LEFT OUTER JOIN VirtualRoomMembers vrm2 --Get the parent rooms
        ON r.room_id = vrm2.sub_room_id
    LEFT OUTER JOIN Reservations rs
        ON (
            r.room_id = rs.room_id OR
            (r.virtual_room = 1 AND vrm.sub_room_id = rs.room_id) --Make sure none of the child rooms are booked
            OR (r.virtual_room = 0 AND vrm2.room_id = rs.room_id) --Make sure the combined parent room isn't booked
        )
        AND rs... (filter the reservations for the date range in question)
WHERE rs.reservation_id IS NULL
Related Question