Hotel Reservation System - The DataJoint Book

Background¶

This example demonstrates a complete hotel management system with rooms, reservations, check-ins, and check-outs. It showcases how DataJoint’s referential integrity enforces business rules automatically through the schema design.

Business Rules¶

The hotel system enforces the following business rules:

The hotel has rooms of two types: Deluxe and Suite
For every night, rooms are made available for reservation at a specific price
A guest can make a reservation for an available room for one night, including credit card payment info. At most one reservation can be made per night per room.
A guest can check into a room only after making a reservation. An attempt to check in without a reservation will generate an error.
A guest can check out only after checking in. Duplicate check-outs or checking out without checking in will generate an error.

Design Concepts Demonstrated¶

Time-series data: Room availability and pricing varies by date
Business rule enforcement: Database constraints prevent double-booking and enforce check-in/check-out sequence
Guest management: Guest records are created on-demand during reservation
Workflow dependencies: CheckIn depends on Reservation, CheckOut depends on CheckIn
Error handling: Custom exceptions provide clear feedback when rules are violated

Schema Definition¶

%xmode minimal
import datajoint as dj
schema = dj.Schema('hotel')

Exception reporting mode: Minimal

[2025-10-11 03:07:53,440][INFO]: DataJoint 0.14.6 connected to dev@db:3306

@schema
class Room(dj.Manual):
    definition = """
    room : int
    --- 
    room_type : enum('Deluxe', 'Suite')
    """

@schema
class RoomAvailable(dj.Manual):
    definition = """
    -> Room
    date : date 
    ---
    price : decimal(6, 2)
    """

@schema
class Guest(dj.Manual):
    definition = """
    guest_id : int unsigned
    --- 
    guest_name : varchar(60)
    index(guest_name)
    """

@schema
class Reservation(dj.Manual):
    definition = """
    -> RoomAvailable
    ---
    -> Guest
    credit_card : varchar(80)
    """

@schema
class CheckIn(dj.Manual):
    definition = """
    -> Reservation
    """

@schema
class CheckOut(dj.Manual):
    definition = """
    -> CheckIn
    """

dj.Diagram(schema)

Populate Room Availability¶

import faker
import random
import datetime
import tqdm
fake = faker.Faker()

# populate rooms
Room.insert((i, "Deluxe" if i%2 else "Suite") for i in range(80))

# Populate Room availability: 45 days starting on start_date
start_date = datetime.date(2023, 11, 1)
days = 45

for day in tqdm.tqdm(range(days)):
    price = random.randint(40, 350) 
    RoomAvailable.insert(
        dict(key, 
             date=start_date + datetime.timedelta(days=day), 
             price=price) for key in Room.fetch('KEY'))

100%|██████████| 45/45 [00:00<00:00, 410.39it/s]

Helper Functions¶

These functions encapsulate the business logic for reservations, check-ins, and check-outs. They use DataJoint’s error handling to enforce business rules:

class HotelException(Exception): pass
class RoomUnavailable(HotelException): pass
class RoomAlreadyReserved(HotelException): pass
class AlreadyChecked(HotelException): pass
class NoReservation(HotelException): pass
class NotCheckedIn(HotelException): pass

def reserve_room(room, date, guest_name, credit_card):
    # lookup guest by name
    keys = (Guest & {'guest_name': guest_name}).fetch('KEY')
    
    if keys:
        # if multiple found, use the first, for example
        key = keys[0]  
    else:
        # if not registered before, create a new guest id
        key = dict(guest_id=random.randint(0, 2**32-1))
        Guest.insert1(dict(key, guest_name=guest_name))
    
    try:
        Reservation.insert1(
            dict(key, room=room, date=date, credit_card=credit_card))
    except dj.errors.DuplicateError:
        raise RoomAlreadyReserved(room, date.isoformat()) from None
    except dj.errors.IntegrityError:
        raise RoomUnavailable(room, date.isoformat()) from None

def check_in(room, date):
    try:
        CheckIn.insert1(dict(room=room, date=date))
    except dj.errors.DuplicateError:
        raise AlreadyChecked(room, date.isoformat()) from None
    except dj.errors.IntegrityError:
        raise NoReservation(room, date.isoformat()) from None

def check_out(room, date):
    try:
        CheckOut.insert1(dict(room=room, date=date))
    except dj.errors.DuplicateError:
        raise AlreadyChecked(room, date.isoformat()) from None
    except dj.errors.IntegrityError:
        raise NotCheckedIn(room, date.isoformat()) from None

Operations Demo¶

Now let’s simulate hotel operations: making reservations, checking guests in, and checking out:

# make a bunch of random reservations

number_of_guests = 100
max_nights = 20

for i in tqdm.tqdm(range(number_of_guests)):
    guest = fake.name()
    credit_card=' '.join((fake.credit_card_number(), 
                          fake.credit_card_expire(), 
                          fake.credit_card_security_code()))
    
    for j in range(random.randint(1, max_nights)):
        date =  fake.date_between_dates(start_date, start_date+datetime.timedelta(days=45))
        room = random.randint(0, 80)
        try:
            reserve_room(room, date, guest, credit_card)
        except HotelException as e:
            print(repr(e))
            
# show successful reservations
Reservation()

 18%|█▊        | 18/100 [00:00<00:01, 79.05it/s]

RoomUnavailable(80, '2023-11-04')
RoomAlreadyReserved(2, '2023-11-19')
RoomUnavailable(80, '2023-12-14')
RoomAlreadyReserved(57, '2023-12-03')
RoomAlreadyReserved(42, '2023-12-10')
RoomAlreadyReserved(19, '2023-11-13')
RoomUnavailable(80, '2023-11-04')

 36%|███▌      | 36/100 [00:00<00:00, 78.84it/s]

RoomAlreadyReserved(78, '2023-11-23')
RoomAlreadyReserved(4, '2023-11-29')
RoomAlreadyReserved(21, '2023-11-01')
RoomAlreadyReserved(76, '2023-11-17')
RoomAlreadyReserved(40, '2023-11-13')
RoomAlreadyReserved(5, '2023-11-03')
RoomAlreadyReserved(23, '2023-11-21')
RoomAlreadyReserved(60, '2023-11-29')
RoomAlreadyReserved(64, '2023-11-07')
RoomAlreadyReserved(46, '2023-11-27')
RoomAlreadyReserved(32, '2023-12-14')
RoomAlreadyReserved(59, '2023-11-26')
RoomAlreadyReserved(55, '2023-11-30')
RoomAlreadyReserved(42, '2023-11-10')
RoomAlreadyReserved(66, '2023-12-02')
RoomAlreadyReserved(42, '2023-12-10')
RoomAlreadyReserved(60, '2023-12-13')
RoomAlreadyReserved(15, '2023-11-25')
RoomAlreadyReserved(11, '2023-12-10')
RoomAlreadyReserved(33, '2023-11-25')
RoomAlreadyReserved(68, '2023-11-01')
RoomAlreadyReserved(30, '2023-11-18')
RoomAlreadyReserved(4, '2023-11-29')
RoomUnavailable(80, '2023-11-30')
RoomAlreadyReserved(13, '2023-12-01')
RoomAlreadyReserved(36, '2023-11-17')
RoomAlreadyReserved(5, '2023-11-30')
RoomAlreadyReserved(45, '2023-11-23')
RoomAlreadyReserved(17, '2023-11-15')
RoomAlreadyReserved(42, '2023-11-07')

 47%|████▋     | 47/100 [00:00<00:00, 86.52it/s]

RoomAlreadyReserved(7, '2023-12-12')
RoomAlreadyReserved(65, '2023-11-09')
RoomAlreadyReserved(78, '2023-11-14')
RoomAlreadyReserved(3, '2023-11-21')
RoomAlreadyReserved(59, '2023-11-03')
RoomAlreadyReserved(55, '2023-12-10')
RoomAlreadyReserved(1, '2023-11-08')
RoomAlreadyReserved(28, '2023-11-03')
RoomAlreadyReserved(62, '2023-12-10')
RoomAlreadyReserved(13, '2023-12-03')
RoomAlreadyReserved(69, '2023-11-27')
RoomAlreadyReserved(24, '2023-12-03')
RoomAlreadyReserved(9, '2023-12-06')
RoomAlreadyReserved(24, '2023-11-13')
RoomAlreadyReserved(34, '2023-11-02')
RoomAlreadyReserved(59, '2023-11-26')
RoomAlreadyReserved(25, '2023-12-12')
RoomAlreadyReserved(38, '2023-12-05')
RoomAlreadyReserved(21, '2023-11-01')
RoomAlreadyReserved(46, '2023-11-09')
RoomAlreadyReserved(53, '2023-11-22')
RoomAlreadyReserved(11, '2023-12-06')
RoomAlreadyReserved(39, '2023-12-15')
RoomAlreadyReserved(8, '2023-11-26')
RoomAlreadyReserved(3, '2023-11-04')
RoomAlreadyReserved(9, '2023-11-02')
RoomAlreadyReserved(8, '2023-11-12')
RoomAlreadyReserved(45, '2023-11-30')
RoomAlreadyReserved(53, '2023-11-26')
RoomAlreadyReserved(73, '2023-11-08')

 65%|██████▌   | 65/100 [00:00<00:00, 82.17it/s]

RoomAlreadyReserved(42, '2023-11-03')
RoomAlreadyReserved(65, '2023-11-11')
RoomAlreadyReserved(71, '2023-12-04')
RoomAlreadyReserved(57, '2023-12-03')
RoomAlreadyReserved(10, '2023-11-18')
RoomAlreadyReserved(66, '2023-12-02')
RoomAlreadyReserved(20, '2023-11-06')
RoomAlreadyReserved(69, '2023-11-14')
RoomUnavailable(80, '2023-11-05')
RoomAlreadyReserved(6, '2023-12-13')
RoomAlreadyReserved(19, '2023-11-23')
RoomAlreadyReserved(30, '2023-11-08')
RoomAlreadyReserved(57, '2023-12-09')
RoomAlreadyReserved(75, '2023-12-13')
RoomAlreadyReserved(15, '2023-11-08')
RoomAlreadyReserved(74, '2023-11-15')
RoomAlreadyReserved(51, '2023-12-05')
RoomAlreadyReserved(6, '2023-11-19')
RoomAlreadyReserved(76, '2023-11-17')
RoomAlreadyReserved(18, '2023-11-09')
RoomAlreadyReserved(4, '2023-11-08')
RoomAlreadyReserved(48, '2023-11-01')
RoomAlreadyReserved(9, '2023-12-14')
RoomAlreadyReserved(27, '2023-11-13')
RoomAlreadyReserved(57, '2023-12-01')
RoomAlreadyReserved(55, '2023-11-02')
RoomAlreadyReserved(57, '2023-11-25')
RoomAlreadyReserved(1, '2023-11-02')
RoomAlreadyReserved(46, '2023-11-13')
RoomAlreadyReserved(51, '2023-11-03')
RoomAlreadyReserved(6, '2023-12-04')
RoomAlreadyReserved(43, '2023-12-08')
RoomAlreadyReserved(55, '2023-11-02')
RoomAlreadyReserved(21, '2023-11-13')
RoomAlreadyReserved(42, '2023-11-17')
RoomAlreadyReserved(51, '2023-12-05')
RoomAlreadyReserved(43, '2023-12-05')
RoomAlreadyReserved(6, '2023-11-27')
RoomAlreadyReserved(46, '2023-11-27')
RoomUnavailable(80, '2023-11-16')
RoomAlreadyReserved(31, '2023-11-27')
RoomAlreadyReserved(20, '2023-11-23')
RoomAlreadyReserved(70, '2023-12-10')

 85%|████████▌ | 85/100 [00:01<00:00, 86.31it/s]

RoomAlreadyReserved(68, '2023-11-01')
RoomAlreadyReserved(55, '2023-11-22')
RoomAlreadyReserved(38, '2023-11-01')
RoomAlreadyReserved(53, '2023-12-13')
RoomAlreadyReserved(68, '2023-12-02')
RoomAlreadyReserved(70, '2023-11-04')
RoomAlreadyReserved(64, '2023-11-01')
RoomAlreadyReserved(16, '2023-12-05')
RoomAlreadyReserved(75, '2023-12-06')
RoomAlreadyReserved(77, '2023-11-10')
RoomAlreadyReserved(29, '2023-12-04')
RoomAlreadyReserved(19, '2023-11-23')
RoomAlreadyReserved(23, '2023-11-30')
RoomAlreadyReserved(26, '2023-12-08')
RoomAlreadyReserved(64, '2023-11-13')
RoomAlreadyReserved(6, '2023-11-04')
RoomAlreadyReserved(0, '2023-12-11')
RoomAlreadyReserved(49, '2023-12-02')
RoomAlreadyReserved(12, '2023-12-04')
RoomAlreadyReserved(39, '2023-11-24')
RoomAlreadyReserved(72, '2023-12-01')
RoomAlreadyReserved(74, '2023-12-10')
RoomAlreadyReserved(20, '2023-11-24')
RoomAlreadyReserved(13, '2023-11-22')
RoomAlreadyReserved(78, '2023-11-14')
RoomAlreadyReserved(55, '2023-12-07')
RoomAlreadyReserved(39, '2023-12-15')
RoomAlreadyReserved(14, '2023-11-04')
RoomAlreadyReserved(3, '2023-11-12')
RoomAlreadyReserved(73, '2023-11-19')
RoomAlreadyReserved(40, '2023-11-27')
RoomAlreadyReserved(54, '2023-11-18')
RoomAlreadyReserved(14, '2023-11-23')
RoomAlreadyReserved(40, '2023-12-07')
RoomAlreadyReserved(53, '2023-11-14')
RoomAlreadyReserved(34, '2023-11-08')
RoomAlreadyReserved(79, '2023-11-05')
RoomAlreadyReserved(19, '2023-11-23')
RoomAlreadyReserved(19, '2023-11-01')
RoomAlreadyReserved(47, '2023-11-27')
RoomAlreadyReserved(41, '2023-11-10')
RoomAlreadyReserved(4, '2023-11-05')
RoomAlreadyReserved(21, '2023-11-23')
RoomAlreadyReserved(39, '2023-12-02')
RoomAlreadyReserved(57, '2023-12-05')
RoomAlreadyReserved(21, '2023-11-17')
RoomAlreadyReserved(53, '2023-11-21')
RoomUnavailable(80, '2023-12-09')
RoomAlreadyReserved(16, '2023-12-15')
RoomAlreadyReserved(54, '2023-11-11')
RoomAlreadyReserved(71, '2023-11-14')
RoomAlreadyReserved(17, '2023-12-04')
RoomAlreadyReserved(38, '2023-11-04')
RoomAlreadyReserved(76, '2023-11-09')
RoomAlreadyReserved(73, '2023-11-05')
RoomAlreadyReserved(43, '2023-11-27')
RoomAlreadyReserved(78, '2023-11-04')
RoomUnavailable(80, '2023-11-28')
RoomAlreadyReserved(27, '2023-11-05')
RoomAlreadyReserved(1, '2023-11-28')
RoomAlreadyReserved(48, '2023-11-01')
RoomAlreadyReserved(9, '2023-12-15')
RoomAlreadyReserved(11, '2023-12-10')
RoomUnavailable(80, '2023-11-15')
RoomAlreadyReserved(27, '2023-12-11')

100%|██████████| 100/100 [00:01<00:00, 88.06it/s]

RoomAlreadyReserved(1, '2023-11-08')
RoomAlreadyReserved(31, '2023-11-28')
RoomAlreadyReserved(2, '2023-12-12')
RoomAlreadyReserved(51, '2023-12-07')
RoomAlreadyReserved(27, '2023-11-27')
RoomAlreadyReserved(58, '2023-11-21')

Guest()

# Try check in
check_in(2, datetime.date(2023, 11, 2))

# checkin a bunch of people
checkins = random.sample(Reservation().fetch('KEY'), k=int(0.9*len(Reservation())))
for r in tqdm.tqdm(checkins):
    try: 
        check_in(**r)
    except AlreadyChecked as e:
        print(repr(e))

 40%|███▉      | 327/819 [00:00<00:00, 1104.34it/s]

AlreadyChecked(2, '2023-11-02')

100%|██████████| 819/819 [00:00<00:00, 1203.70it/s]

CheckIn()

# Try duplicate checkin -- should fail 
check_in(**checkins[0])

AlreadyChecked: (7, '2023-12-12')

# Try checkout

check_out(2, datetime.date(2023, 10, 2))

NotCheckedIn: (2, '2023-10-02')

# checkout a bunch of people
checkouts = random.sample(CheckIn().fetch('KEY'), k=int(0.9*len(CheckIn())))
for r in tqdm.tqdm(checkouts):
    try:
        check_out(**r)
    except AlreadyChecked as e:
        print(repr(e))

100%|██████████| 737/737 [00:00<00:00, 1450.70it/s]

# try duplicate checkout -- should fail

check_out(**checkouts[0])

AlreadyChecked: (28, '2023-11-29')