Hotel Reservation System¶
This example demonstrates how DataJoint's schema design enforces business rules automatically. You'll learn:
- Workflow dependencies — Tables that enforce operational sequences
- Business rule enforcement — Using referential integrity as validation
- Temporal data — Room availability and pricing by date
- Error handling — Converting database errors to domain exceptions
The Problem¶
A hotel needs to enforce these business rules:
- Rooms have types (Deluxe, Suite) with varying prices by date
- Guests can only reserve rooms that are available
- Each room can have at most one reservation per night
- Guests must have a reservation before checking in
- Guests must check in before checking out
Traditional approaches validate these rules in application code. With DataJoint, the schema itself enforces them through foreign keys and unique constraints.
In [1]:
Copied!
import datajoint as dj
import datetime
import random
# Clean start
schema = dj.Schema('tutorial_hotel')
schema.drop(prompt=False)
schema = dj.Schema('tutorial_hotel')
import datajoint as dj
import datetime
import random
# Clean start
schema = dj.Schema('tutorial_hotel')
schema.drop(prompt=False)
schema = dj.Schema('tutorial_hotel')
[2026-02-06 11:46:35] DataJoint 2.1.0 connected to datajoint@127.0.0.1:5432
Schema Design¶
The schema forms a workflow: Room → RoomAvailable → Reservation → CheckIn → CheckOut
Each arrow represents a foreign key dependency. You cannot insert a child record without a valid parent.
In [2]:
Copied!
@schema
class Room(dj.Lookup):
definition = """
# Hotel rooms
room : int16 # room number
---
room_type : enum('Deluxe', 'Suite')
"""
contents = [
{'room': i, 'room_type': 'Suite' if i % 5 == 0 else 'Deluxe'}
for i in range(1, 21) # 20 rooms
]
@schema
class Room(dj.Lookup):
definition = """
# Hotel rooms
room : int16 # room number
---
room_type : enum('Deluxe', 'Suite')
"""
contents = [
{'room': i, 'room_type': 'Suite' if i % 5 == 0 else 'Deluxe'}
for i in range(1, 21) # 20 rooms
]
In [3]:
Copied!
@schema
class RoomAvailable(dj.Manual):
definition = """
# Room availability and pricing by date
-> Room
date : date
---
price : decimal(6, 2) # price per night
"""
@schema
class RoomAvailable(dj.Manual):
definition = """
# Room availability and pricing by date
-> Room
date : date
---
price : decimal(6, 2) # price per night
"""
In [4]:
Copied!
@schema
class Guest(dj.Manual):
definition = """
# Hotel guests
guest_id : int64 # auto-assigned guest ID
---
guest_name : varchar(60)
index(guest_name)
"""
@schema
class Guest(dj.Manual):
definition = """
# Hotel guests
guest_id : int64 # auto-assigned guest ID
---
guest_name : varchar(60)
index(guest_name)
"""
In [5]:
Copied!
@schema
class Reservation(dj.Manual):
definition = """
# Room reservations (one per room per night)
-> RoomAvailable
---
-> Guest
credit_card : varchar(80) # encrypted card info
"""
@schema
class Reservation(dj.Manual):
definition = """
# Room reservations (one per room per night)
-> RoomAvailable
---
-> Guest
credit_card : varchar(80) # encrypted card info
"""
In [6]:
Copied!
@schema
class CheckIn(dj.Manual):
definition = """
# Check-in records (requires reservation)
-> Reservation
"""
@schema
class CheckIn(dj.Manual):
definition = """
# Check-in records (requires reservation)
-> Reservation
"""
In [7]:
Copied!
@schema
class CheckOut(dj.Manual):
definition = """
# Check-out records (requires check-in)
-> CheckIn
"""
@schema
class CheckOut(dj.Manual):
definition = """
# Check-out records (requires check-in)
-> CheckIn
"""
In [8]:
Copied!
dj.Diagram(schema)
dj.Diagram(schema)
Out[8]:
How the Schema Enforces Rules¶
| Business Rule | Schema Enforcement |
|---|---|
| Room must exist | Reservation -> RoomAvailable -> Room |
| Room must be available on date | Reservation -> RoomAvailable |
| One reservation per room/night | RoomAvailable is primary key of Reservation |
| Must reserve before check-in | CheckIn -> Reservation |
| Must check-in before check-out | CheckOut -> CheckIn |
The database rejects invalid operations — no application code needed.
Populate Room Availability¶
Make rooms available for the next 30 days with random pricing:
In [9]:
Copied!
random.seed(42)
start_date = datetime.date.today()
days = 30
for day in range(days):
date = start_date + datetime.timedelta(days=day)
# Weekend prices are higher
is_weekend = date.weekday() >= 5
base_price = 200 if is_weekend else 150
RoomAvailable.insert(
{
'room': room['room'],
'date': date,
'price': base_price + random.randint(-30, 50)
}
for room in Room.to_dicts()
)
print(f"Created {len(RoomAvailable())} room-night records")
RoomAvailable() & {'room': 1}
random.seed(42)
start_date = datetime.date.today()
days = 30
for day in range(days):
date = start_date + datetime.timedelta(days=day)
# Weekend prices are higher
is_weekend = date.weekday() >= 5
base_price = 200 if is_weekend else 150
RoomAvailable.insert(
{
'room': room['room'],
'date': date,
'price': base_price + random.randint(-30, 50)
}
for room in Room.to_dicts()
)
print(f"Created {len(RoomAvailable())} room-night records")
RoomAvailable() & {'room': 1}
Created 600 room-night records
Out[9]:
| room | date | price |
|---|---|---|
| 1 | 2026-02-06 | 134.00 |
| 1 | 2026-02-07 | 195.00 |
| 1 | 2026-02-08 | 214.00 |
| 1 | 2026-02-09 | 130.00 |
| 1 | 2026-02-10 | 154.00 |
| 1 | 2026-02-11 | 137.00 |
| 1 | 2026-02-12 | 174.00 |
| 1 | 2026-02-13 | 120.00 |
| 1 | 2026-02-14 | 172.00 |
| 1 | 2026-02-15 | 247.00 |
| 1 | 2026-02-16 | 195.00 |
| 1 | 2026-02-17 | 180.00 |
...
Total: 30
Business Operations¶
These functions wrap database operations and convert constraint violations into meaningful domain errors:
In [10]:
Copied!
# Domain-specific exceptions
class HotelError(Exception):
pass
class RoomNotAvailable(HotelError):
pass
class RoomAlreadyReserved(HotelError):
pass
class NoReservation(HotelError):
pass
class NotCheckedIn(HotelError):
pass
class AlreadyProcessed(HotelError):
pass
# Domain-specific exceptions
class HotelError(Exception):
pass
class RoomNotAvailable(HotelError):
pass
class RoomAlreadyReserved(HotelError):
pass
class NoReservation(HotelError):
pass
class NotCheckedIn(HotelError):
pass
class AlreadyProcessed(HotelError):
pass
In [11]:
Copied!
def reserve_room(room, date, guest_name, credit_card):
"""
Make a reservation. Creates guest record if needed.
Raises
------
RoomNotAvailable
If room is not available on that date
RoomAlreadyReserved
If room is already reserved for that date
"""
# Find or create guest
guests = list((Guest & {'guest_name': guest_name}).keys())
if guests:
guest_key = guests[0]
else:
guest_key = {'guest_id': random.randint(1, 2**31)}
Guest.insert1({**guest_key, 'guest_name': guest_name})
try:
Reservation.insert1({
'room': room,
'date': date,
**guest_key,
'credit_card': credit_card
})
except dj.errors.DuplicateError:
raise RoomAlreadyReserved(
f"Room {room} already reserved for {date}") from None
except dj.errors.IntegrityError:
raise RoomNotAvailable(
f"Room {room} not available on {date}") from None
def reserve_room(room, date, guest_name, credit_card):
"""
Make a reservation. Creates guest record if needed.
Raises
------
RoomNotAvailable
If room is not available on that date
RoomAlreadyReserved
If room is already reserved for that date
"""
# Find or create guest
guests = list((Guest & {'guest_name': guest_name}).keys())
if guests:
guest_key = guests[0]
else:
guest_key = {'guest_id': random.randint(1, 2**31)}
Guest.insert1({**guest_key, 'guest_name': guest_name})
try:
Reservation.insert1({
'room': room,
'date': date,
**guest_key,
'credit_card': credit_card
})
except dj.errors.DuplicateError:
raise RoomAlreadyReserved(
f"Room {room} already reserved for {date}") from None
except dj.errors.IntegrityError:
raise RoomNotAvailable(
f"Room {room} not available on {date}") from None
In [12]:
Copied!
def check_in(room, date):
"""
Check in a guest. Requires existing reservation.
Raises
------
NoReservation
If no reservation exists for this room/date
AlreadyProcessed
If guest already checked in
"""
try:
CheckIn.insert1({'room': room, 'date': date})
except dj.errors.DuplicateError:
raise AlreadyProcessed(
f"Room {room} already checked in for {date}") from None
except dj.errors.IntegrityError:
raise NoReservation(
f"No reservation for room {room} on {date}") from None
def check_in(room, date):
"""
Check in a guest. Requires existing reservation.
Raises
------
NoReservation
If no reservation exists for this room/date
AlreadyProcessed
If guest already checked in
"""
try:
CheckIn.insert1({'room': room, 'date': date})
except dj.errors.DuplicateError:
raise AlreadyProcessed(
f"Room {room} already checked in for {date}") from None
except dj.errors.IntegrityError:
raise NoReservation(
f"No reservation for room {room} on {date}") from None
In [13]:
Copied!
def check_out(room, date):
"""
Check out a guest. Requires prior check-in.
Raises
------
NotCheckedIn
If guest hasn't checked in
AlreadyProcessed
If guest already checked out
"""
try:
CheckOut.insert1({'room': room, 'date': date})
except dj.errors.DuplicateError:
raise AlreadyProcessed(
f"Room {room} already checked out for {date}") from None
except dj.errors.IntegrityError:
raise NotCheckedIn(
f"Room {room} not checked in for {date}") from None
def check_out(room, date):
"""
Check out a guest. Requires prior check-in.
Raises
------
NotCheckedIn
If guest hasn't checked in
AlreadyProcessed
If guest already checked out
"""
try:
CheckOut.insert1({'room': room, 'date': date})
except dj.errors.DuplicateError:
raise AlreadyProcessed(
f"Room {room} already checked out for {date}") from None
except dj.errors.IntegrityError:
raise NotCheckedIn(
f"Room {room} not checked in for {date}") from None
Demo: Business Rule Enforcement¶
Let's see the schema enforce our business rules:
In [14]:
Copied!
# Successful reservation
tomorrow = start_date + datetime.timedelta(days=1)
reserve_room(1, tomorrow, 'Alice Smith', '4111-1111-1111-1111')
print(f"Reserved room 1 for {tomorrow}")
Reservation()
# Successful reservation
tomorrow = start_date + datetime.timedelta(days=1)
reserve_room(1, tomorrow, 'Alice Smith', '4111-1111-1111-1111')
print(f"Reserved room 1 for {tomorrow}")
Reservation()
Reserved room 1 for 2026-02-07
Out[14]:
| room | date | guest_id | credit_card |
|---|---|---|---|
| 1 | 2026-02-07 | 1282531583 | 4111-1111-1111-1111 |
Total: 1
In [15]:
Copied!
# Try to double-book the same room — fails!
try:
reserve_room(1, tomorrow, 'Bob Jones', '5555-5555-5555-5555')
except RoomAlreadyReserved as e:
print(f"Blocked: {e}")
# Try to double-book the same room — fails!
try:
reserve_room(1, tomorrow, 'Bob Jones', '5555-5555-5555-5555')
except RoomAlreadyReserved as e:
print(f"Blocked: {e}")
Blocked: Room 1 already reserved for 2026-02-07
In [17]:
Copied!
# Try to check in without reservation — fails!
try:
check_in(2, tomorrow) # Room 2 has no reservation
except NoReservation as e:
print(f"Blocked: {e}")
# Try to check in without reservation — fails!
try:
check_in(2, tomorrow) # Room 2 has no reservation
except NoReservation as e:
print(f"Blocked: {e}")
Blocked: No reservation for room 2 on 2026-02-07
In [18]:
Copied!
# Successful check-in (has reservation)
check_in(1, tomorrow)
print(f"Checked in room 1 for {tomorrow}")
CheckIn()
# Successful check-in (has reservation)
check_in(1, tomorrow)
print(f"Checked in room 1 for {tomorrow}")
CheckIn()
Checked in room 1 for 2026-02-07
Out[18]:
| room | date |
|---|---|
| 1 | 2026-02-07 |
Total: 1
In [19]:
Copied!
# Try to check out without checking in — fails!
# First make a reservation for room 3
reserve_room(3, tomorrow, 'David Brown', '7777-7777-7777-7777')
try:
check_out(3, tomorrow) # Reserved but not checked in
except NotCheckedIn as e:
print(f"Blocked: {e}")
# Try to check out without checking in — fails!
# First make a reservation for room 3
reserve_room(3, tomorrow, 'David Brown', '7777-7777-7777-7777')
try:
check_out(3, tomorrow) # Reserved but not checked in
except NotCheckedIn as e:
print(f"Blocked: {e}")
Blocked: Room 3 not checked in for 2026-02-07
In [20]:
Copied!
# Successful check-out (was checked in)
check_out(1, tomorrow)
print(f"Checked out room 1 for {tomorrow}")
CheckOut()
# Successful check-out (was checked in)
check_out(1, tomorrow)
print(f"Checked out room 1 for {tomorrow}")
CheckOut()
Checked out room 1 for 2026-02-07
Out[20]:
| room | date |
|---|---|
| 1 | 2026-02-07 |
Total: 1
Useful Queries¶
The workflow structure enables powerful queries:
In [21]:
Copied!
# Available rooms (not reserved) for tomorrow
available = (RoomAvailable & {'date': tomorrow}) - Reservation
print(f"Available rooms for {tomorrow}: {len(available)}")
available
# Available rooms (not reserved) for tomorrow
available = (RoomAvailable & {'date': tomorrow}) - Reservation
print(f"Available rooms for {tomorrow}: {len(available)}")
available
Available rooms for 2026-02-07: 18
Out[21]:
| room | date | price |
|---|---|---|
| 2 | 2026-02-07 | 239.00 |
| 4 | 2026-02-07 | 198.00 |
| 5 | 2026-02-07 | 227.00 |
| 6 | 2026-02-07 | 245.00 |
| 7 | 2026-02-07 | 205.00 |
| 8 | 2026-02-07 | 170.00 |
| 9 | 2026-02-07 | 190.00 |
| 10 | 2026-02-07 | 224.00 |
| 11 | 2026-02-07 | 213.00 |
| 12 | 2026-02-07 | 205.00 |
| 13 | 2026-02-07 | 189.00 |
| 14 | 2026-02-07 | 197.00 |
...
Total: 18
In [22]:
Copied!
# Guests currently checked in (checked in but not out)
currently_in = (CheckIn - CheckOut) * Reservation * Guest
currently_in.proj('guest_name', 'room', 'date')
# Guests currently checked in (checked in but not out)
currently_in = (CheckIn - CheckOut) * Reservation * Guest
currently_in.proj('guest_name', 'room', 'date')
Out[22]:
| room | date | guest_name |
|---|---|---|
Total: 0
In [23]:
Copied!
# Reservations without check-in (no-shows or upcoming)
not_checked_in = Reservation - CheckIn
(not_checked_in * Guest).proj('guest_name', 'room', 'date')
# Reservations without check-in (no-shows or upcoming)
not_checked_in = Reservation - CheckIn
(not_checked_in * Guest).proj('guest_name', 'room', 'date')
Out[23]:
| room | date | guest_name |
|---|---|---|
| 3 | 2026-02-07 | David Brown |
Total: 1
In [24]:
Copied!
# Revenue by room type using aggr
dj.U('room_type').aggr(
Room * RoomAvailable * Reservation,
total_revenue='SUM(price)',
reservations='COUNT(*)'
)
# Revenue by room type using aggr
dj.U('room_type').aggr(
Room * RoomAvailable * Reservation,
total_revenue='SUM(price)',
reservations='COUNT(*)'
)
Out[24]:
| room_type | total_revenue | reservations |
|---|---|---|
| Deluxe | 418.00 | 2 |
Total: 1
Key Concepts¶
| Concept | How It's Used |
|---|---|
| Workflow Dependencies | CheckOut -> CheckIn -> Reservation -> RoomAvailable |
| Unique Constraints | One reservation per room/night (primary key) |
| Referential Integrity | Can't reserve unavailable room, can't check in without reservation |
| Error Translation | Database exceptions → domain-specific errors |
The schema is the business logic. Application code just translates errors.
Next Steps¶
- University Database — Academic records with many-to-many relationships
- Languages & Proficiency — International standards and lookup tables
- Data Entry — Insert patterns and transactions
In [25]:
Copied!
# Cleanup
schema.drop(prompt=False)
# Cleanup
schema.drop(prompt=False)