How I Would Design The Data Model For A Hotel Booking App With DynamoDB
Using the single table design to design a hotel booking platform database.
Imagine you are running a hotel application that allows guests to view properties and book rooms that you manage.
Think of it like Hotels.com or Booking.com.
The main purpose is to build a scalable database that will support user queries, handle bookings and view upcoming bookings.
Let’s see how we can design this database to be ultra-scalable with DynamoDB.
Identifying Data Access Patterns
The first step in my process for any data model with DynamoDB, is always to determine the data access patterns.
In other words, what kind of queries would guests be doing most commonly?
Figuring these out and brainstorming on these, and optimizing these, is the most important step in any successful DynamoDB database design.
Here are some common access patterns examples:
Users can browse the list of properties by destination
Users can browse the list of rooms for a given property
Users can view the room details for a given hotel
Users can get rooms by type, e.g. Sea view room
Users can view their bookings
An administrator can view all bookings in a given hotel
Once we validate our access patterns we can identify the data entities:
Hotels
Users
Booking
Rooms
Now that we know how users will be accessing our data and the types of entities, we can start designing a data model in a single table design.
Single Table Data Model
Here’s the data model I came up with — it prioritizes relational data and hence lets us make fewer queries and optimize costs:
The user items are highlighted in yellow, an item holds some basic information on the user such as name, email, payment details, etc.
The hotel items are highlighted in green, a hotel item holds some basic information (only) on a hotel, such as rating, hotelID, the lowest room price, etc.
The room items are shown in red and have an interesting primary key (pk) design — the partition key is made up of the hotel ID with the suffix “room”. The sort key (sk) is made up of the room ID, the type of room and the number of allowed guests, to enable efficient filtering of rooms.
Finally, highlighted in blue are the booking items which are partitioned by userID and sort key is made up of the date of the booking (for sorting purposes) and suffixed by the bookingID.
If you go through this data model and know enough about DynamoDB you can see that we can satisfy each one of our 6 access patterns with this data model.
Let’s see exactly how we can do that below.
Data Access Patterns Queries
With an example data model we can now move on to designing efficient queries to satisfy our access patterns.
1. Users can browse the list of properties by destination
Let’s design the query to let users browse a list of properties.
When a user searches for hotels, they usually do it by entering a destination first.
If the user enters “Paros, Greece” for example, we can display all the properties in that location.
We’d need to query our GSI for this, here’s an example code snippet:
TableName: "hotel-app",
IndexName: "GSI1",
KeyConditionExpression: "GSI1PK = greece-paros AND begins_with(sk, 'hotel#')"We use the begins_with method to get all properties in the “greece-paros” partition, whose sk value begins with “hotel#”. Which is all properties in that destination.
If we need to get a specific hotel item only we can swap out the begins_with() method for a strict equals operation and pass in the hotelID.
2. Users can browse the list of rooms for a given property
To get all rooms for a given property we can use a similar logic. We will access the hotelID partition, like so:
(We don’t need to use the GSI this time).
TableName: "hotel-app",
KeyConditionExpression: "pk = hotel#101 AND begins_with(sk, 'room#')"And that will give us all the rooms inside that hotel.
If we wanted to get a single room’s details instead, we can again swap out the begins_with() method for a strict equals operation and pass in the roomID.
3. Users can view the room details for a given hotel
To view the room details like price, rating and room type, we can pass the hotelID and roomID and query a single item:
TableName: "hotel-app",
KeyConditionExpression: "pk = hotel#101 AND sk = 'room#sea-view#2-guests#201'"To be clear the roomID here is designed as the room type and guests number, suffixed with the room number — e.g. “room#sea-view#2-guests#201”.
Keep that in mind when creating room items.
Why is this a good design pattern?
Let’s see why in the next access pattern.
4. Users can get rooms by type, e.g. Sea view room
With the sort key design we have on the room items, we can enable powerful and efficient filtering queries such as “get all rooms that have a sea view”, for instance.
Here’s how:
TableName: "hotel-app",
KeyConditionExpression: "pk = hotel#101 AND begins_with(sk, 'room#sea-view#')"We can also further filter by number of guests allowed:
TableName: "hotel-app",
KeyConditionExpression: "pk = hotel#101 AND begins_with(sk, 'room#sea-view#2-guests#')"5. Users can view their bookings
Let’s satisfy the access pattern of letting users view bookings they have made.
For this, we again have enabled efficient filtering through our data model.
We can do the following:
get all bookings made
get all bookings filtered by date (e.g. on 2025, in June, on a given date)
Get all bookings within a specified date range (e.g. from January to march)
Get a specific booking by bookingID
Get all bookings:
TableName: "hotel-app",
KeyConditionExpression: "pk = user#101 AND begins_with(sk, 'date#')"Get all bookings in 2025:
TableName: "hotel-app",
KeyConditionExpression: "pk = user#101 AND sk BETWEEN 'date#202501010000#' AND 'date#202512311159\uffff'"Get all bookings on June 2025:
TableName: "hotel-app",
KeyConditionExpression: "pk = user#101 AND sk BETWEEN 'date#202506010000#' AND 'date#202506301159\uffff'"Get all bookings between Janurary and March 2025:
TableName: "hotel-app",
KeyConditionExpression: "pk = user#101 AND sk BETWEEN 'date#202501010000#' AND 'date#202503311159\uffff'"You can get as fine-grained as you want here and really have a lot of flexibility with dates.
6. An administrator can view all bookings in a given hotel
Finally, the last access pattern we can satisfy with a query will be using the GSI once again.
Notice all bookings have GSI1PK and GSI1SK values. We can use these to query for bookings based on hotels.
TableName: "hotel-app",
IndexName: "GSI1",
KeyConditionExpression: "GSI1PK = hotel#101 AND begins_with(sk, 'room#')"Again we can get more filtering here by using room types, etc.
Summary
In this article, I explain the single table design and data modeling techniques to efficiently model a hotel booking database.
Data modeling with DynamoDB requires you to understand your access patterns well in advance.
Your data model should be oriented to satisfy these access patterns.
Overloading keys, like we saw above, also allows you to enable advanced and efficient filtering of items at scale.
👋 My name is Uriel Bitton and I’m committed to helping you master Serverless, Cloud Computing, and AWS.
🚀 If you want to learn how to build serverless, scalable, and resilient applications, you can also follow me on Linkedin for valuable daily posts.
Thanks for reading and see you in the next one!