- Schema Book
- Theater Reservation
Theater Reservation
This schema pattern is based around the concept of buying tickets for a theater. It’s a reservation-based schema where the user can pick their own seats and theater session, and the seats are reserved until the user either checks out the cart or the cart expires.
It’s a variation of the e-commerce shopping cart for reservations, but has some unique twists to it that makes it a useful pattern to know.
Schema Observations
- Allows for any type of seated venue layout for reservation.
Schema
This schema has different components. We have a collection called theaters that contain documents representing theaters. For simplicity’s sake, each theater only has a single scene. Below is an example document for the theater The Royal.
{
"_id" : 1
, "name" : "The Royal"
, "seats" : [ [ 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 ]
, [ 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 ]
, [ 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 ]
, [ 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 ]
, [ 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 ] ]
, "seatsAvailable" : 80
}
The schema is fairly straightforward.
| Schema Attribute | Description |
|---|---|
| name | Name of the theater |
| seats | A 2d array, representing the available seating in the theater |
| seatsAvailable | The total number of seats in the theater |
The most interesting field is the seats field, which contains the map of the theater. It’s worth noticing that although we have picked a completely uniform seat layout with the same number of seats in each row, this schema would allow you to create any layout you wish. For example, you could have the following seat layout.
, "seats" : [
, [ 0 ]
, [ 0, 0 ]
, [ 0, 0, 0 ]
, [ 0, 0, 0, 0 ]
, [ 0, 0, 0, 0, 0 ] ]
Each theater has multiple sessions representing an actual play session. In this schema these are represented by documents in the sessions collection. Below is an example of session for The Royal theater.
{
"_id" : ObjectId("5500632d2dc02be024ba5c66")
, "theaterId" : 1
, "name" : "Action Movie 5"
, "description" : "Another action movie"
, "start" : ISODate("2015-03-11T15:45:49.103Z")
, "end" : ISODate("2015-03-11T15:45:49.103Z")
, "price" : 10
, "seatsAvailable" : 80
, "seats" : [ [ 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 ]
, [ 0, 0, 0, 0, 0, 1, 1, 1, 0, 0, 0, 0, 0, 0, 0, 0 ]
, [ 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 ]
, [ 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 ]
, [ 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 ] ]
, "reservations" : [ {
"_id": ObjectId("5500632d2dc02be024ba5c66")
, "seats": [ [ 1, 5 ], [ 1, 6 ], [ 1, 7 ] ]
, "price" : 10
, "total" : 30
} ]
}
The schema for a session contains the following fields.
| Schema Attribute | Description |
|---|---|
| theaterId | The Id of the theater the session belongs to |
| name | The name of the show |
| description | The description of the show |
| start | When does the show start |
| end | When does the show end |
| price | The price of the show |
| seatsAvailable | The number of seats left in this session |
| seats | The 2d array seat map, showing which seats have been booked |
| reservations | Any current reservations for this session that are in a cart |
Notice that the seats field mirrors the seating layout of its parent theater document. In a session, it’s used to keep track of what seats have been reserved. The reservations array is an array of current reservations for these sessions before the carts have been checked out.
The shopping cart is fairly straight forward. Let’s look at an example cart with a single reservation.
{
"_id" : 1
, "state" : "canceled"
, "total" : 30
, "reservations" : [ {
"sessionId" : ObjectId("5500632d2dc02be024ba5c66")
, "seats" : [ [ 1, 5 ], [ 1, 6 ], [ 1, 7 ] ]
, "price" : 10
, "total" : 30
} ]
, "modifiedOn" : ISODate("2015-03-11T15:45:49.105Z")
, "createdOn" : ISODate("2015-03-11T15:45:49.104Z")
}
The schema for a cart contains the following fields.
| Schema Attribute | Description |
|---|---|
| state | The current state of the cart, one of active, done, canceled or expired |
| total | The total price of the cart |
| reservations | An array of reservation documents for sessions. Each document includes the sessionId, the seats reserved, the price per seat and the total for that reservation |
| modifiedOn | The date the last time the cart was modified |
| createdOn | The date the cart was created |
The most interesting field here is the reservations field, which contains all the current reservations for the cart. Each reservation is for a specific session in a theater.
Operations
Create a new session
Creating a new session for a specific theater requires us to use the original theater template to create a new session. Let’s consider that we have a theater document with _id of 1 and we are creating a new session that will show Action Movie 5.
var theaterId = 1;
var theaters = db.getSisterDB("booking").theaters;
var sessions = db.getSisterDB("booking").sessions;
var theater = theaters.findOne({"_id": theaterId});
sessions.insert({
, "name" : "Action Movie 5"
, "description" : "Another action movie"
, "start" : ISODate("2015-03-11T15:00:00.000Z")
, "end" : ISODate("2015-03-11T16:00:00.000Z")
, "price" : 10
, "seatsAvailable" : theater.seatsAvailable
, "seats" : theater.seats
, "reservations" : []
});
Reserving seats
We are going to reserve some seats for a specific session. To do this, we first need to establish if the session has the desired seats available before adding them to the cart. We are going to reserve the 6th and 7th seats on the 2nd row. (Remember that we are 0 indexed in the arrays and that the first row is the first array) Looking at the seat map for the session, we have marked the seats we are trying to reserve with an X.
{
...
, "seats" : [ [ 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 ]
, [ 0, 0, 0, 0, 0, X, X, 0, 0, 0, 0, 0, 0, 0, 0, 0 ]
, [ 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 ]
, [ 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 ]
, [ 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 ] ]
...
}
var sessionId = 1;
var cartId = 1;
var seats = [[1, 5], [1, 6]];
var seatsQuery = [];
var setSeatsSelection = {};
var sessions = db.getSisterDB("booking").sessions;
var session = sessions.find({_id: sessionId});
for(var i = 0; i < seats.length; i++) {
var seatSelector = {};
var seatSelection = 'seats.' + seats[i][0] + '.' + seats[i][1];
// Part of $and query to check if seat is free
seatSelector[seatSelection] = 0;
seatsQuery.push(seatSelector);
// Part of $set operation to set seat as occupied
setSeatsSelection[seatSelection] = 1;
}
var result = sessions.update({
_id: sessionId
, $and: seatsQuery
}, {
$set: setSeatsSelection
, $inc: { seatsAvailable: -seats.length }
, $push: {
reservations: {
_id: cartId
, seats: seats
, price: session.price
, total: session.price * seats.length
}
}
});
// Failed to reserve seats
if(result.nModified == 0) ...
// Reservation was successful
if(result.nModified == 1) ...
If the result returns a nModified with the value of 1, we’ve successfully reserved seats in the session. If nModified equals 0, we could not honor the reservation request (some seats might have been sold out). At this point, an application might choose to retrieve the session document to show the updated view of available seating.
It might look more complicated than previous schemas, but it’s fairly straightforward when you break it down. The main thing to remember is that we need to ensure that all seats can be reserved at the same time. To achieve this, we use the $and operator to create an array of all the seats that need to be free in order for the update to be successful. If the selector matches, we need to update all those seats using the $set operator, which that takes a document of key value pairs. The actual update statement we create looks like this.
sessions.update({
_id: 1
, $and: [{'seats.1.5': 0}, {'seats.1.6': 0}]
}, {
$set: {'seats.1.5': 1, 'seats.1.6': 1}
, $inc: { seatsAvailable: 2 }
, $push: {
reservations: {
_id: 1
, seats: [[1, 5], [1, 6]]
, price: 10
, total: 20
}
}
});
Update cart on successful session reservation
We successfully reserved the seats. Now we need to add those reservations to the shopping cart.
var sessionId = 1;
var cartId = 1;
var seats = [[1, 5], [1, 6]];
var seatsQuery = [];
var setSeatsSelection = {};
var sessions = db.getSisterDB("booking").sessions;
var carts = db.getSisterDB("booking").carts;
var session = sessions.find({_id: sessionId});
var result = carts.update({
_id: cartId
}, {
$push: {
reservations: {
sessionId: sessionId
, seats: seats
, price: session.price
, total: session.price * seats.length
}
}
, $inc: { total: session.price * seats.length }
, $set: { modifiedOn: new Date() }
})
// Failed to add reservation to cart
if(result.nModified == 0) ...
// Successfully added reservation to cart
if(result.nModified == 1) ...
As in the previous operation where we tried to reserve seats in a specific session, if nModified equals 1, it means we successfully managed to add the reservation to the cart. If nModified equals 0, we failed to add the reservation (maybe the cart got destroyed). If we failed to add the reservation to the cart, we need to release the reservation on the session document.
Release the seat reservation for a session
To release the reservation, we need to set all the seats we marked with 1 back to 0 for the previously reserved seats.
var sessionId = 1;
var cartId = 1;
var seats = [[1, 5], [1, 6]];
var setSeatsSelection = {};
for(var i = 0; i < seats.length; i++) {
setSeatsSelection['seats.' + seats[i][0] + '.' + seats[i][1]] = 0;
}
var sessions = db.getSisterDB("booking").sessions;
var result = sessions.update({
_id: sessionId
}, {
$set: setSeatsSelection
, $pull: { reservations: { _id: cartId } }
});
// Failed to release reservation
if(result.nModified == 0) ...
// Succeded in releasing reservation
if(result.nModified == 1) ...
To release the reservation we set all the seats in the reservation to 0 and pull the cart reservation from the reservations array in the sessions documents.
Checkout cart
The checkout process consists of a couple of steps. The first one is to create a receipt document in the receipt collection. After creating the receipt, we commit all the reservations on the session before finally setting the cart to done. Let’s look at how we create a receipt document.
var cartId = 1;
var carts = db.getSisterDB("booking").carts;
var receipts = db.getSisterDB("booking").receipts;
var cart = carts.findOne({_id: cartId});
receipts.insert({
createdOn: new Date()
, reservations: cart.reservations
, total: cart.total
});
Next, we need to remove all the reservations in the cart from the sessions so as to finalize the reservation of the seats.
var cartId = 1;
var sessions = db.getSisterDB("booking").sessions;
session.update({
'reservations._id': cartId
}, {
$pull: { reservations: { _id: id } }
}, false, true);
This pulls out all the reservations in all sessions where the reservations._id field matches the cartId.
Finally, we mark the cart as done.
var cartId = 1;
var carts = db.getSisterDB("booking").carts;
carts.update({
_id: cartId
}, {
$set: { state: 'done' }
});
Expire carts
If carts are abandoned but contain reservations, we need to release those reservations again so other customers can have the opportunity of reserving them.
var cutOffDate = new Date();
cutOffDate.setMinutes(cutOffDate.getMinutes() - 30);
var cartsCol = db.getSisterDB("booking").carts;
var sessionsCol = db.getSisterDB("booking").sessions;
var carts = cartsCol.find({
modifiedOn: { $lte: cutOffDate }, state: 'active'
});
// Process all carts
while(carts.hasNext()) {
var cart = carts.next();
// Process all reservations in the cart
for(var i = 0; i < cart.reservations.length; i++) {
var reservation = cart.reservations[i];
var seats = reservation.seats;
var setSeatsSelection = {};
for(var i = 0; i < seats.length; i++) {
setSeatsSelection['seats.' + seats[i][0] + '.' + seats[i][1]] = 0;
}
// Release seats and remove reservation
sessionsCol.update({
_id: reservation._id
}, {
$set: setSeatsSelection
, $pull: { reservations: { _id: cart._id }}
});
}
// Set the cart to expired
cartsCol.update({
_id: cart._id
}, {
$set: { status: 'expired' }
});
}
The first step is to retrieve all the expired carts. Next we return all the reservations in the carts to their respective sessions (setting the released seats to 0 in the session documents). Finally we set the cart to the expired state.
This will correctly release any reservations held by timed out shopping carts.
Indexes
Besides using the _id field to query for carts in the carts collection, we only use the state field for when we are processing expired carts. We should add an index on this field to make the expired carts queries as efficient as possible.
var col = db.getSisterDB("booking").carts;
col.ensureIndex({state:1});
state field index
For the sessions collection we look up sessions by the reservations._id field, so we should add a multikey index for this field.
var col = db.getSisterDB("booking").sessions;
col.ensureIndex({"reservations._id":1});
reservations._id field index
For the theaters and receipts collection we only access documents by the _id field in this example so no additional indexes are needed.
Scaling
Secondary Reads
All the operations against the cart are write operations so secondary reads are not useful for this schema as they might show out of date carts.
The theaters could be read from secondaries but the sessions should be read from the primary, otherwise they might return an out of date view of the current state of reserved seats.
Sharding
Sharding the carts is fairly straight forward, as each cart is self-contained. The easiest shard key for this is a hashed _id key to allow for writing to be as randomly distributed as possible using all the shards.
var admin = db.getSisterDB("admin");
db.runCommand({enableSharding:'booking'});
db.runCommand({
shardCollection: 'booking.carts'
, key: {_id:'hashed'}
});
You could also shard the theater and sessions as they are self-contained documents. A good key here would be the hashed _id key.
Performance
A simple exploration of the performance on a single machine with MongoDb 3.0 shows the difference between MMAP and WiredTiger for a narrow simulation using the schema simulation framework mongodb-schema-simulator.
Scenario
MongoDb runs locally on a MacBook Pro Retina 2015 with ssd and 16 gb ram. The simulation runs with the following parameters against a single mongodb instance under osx 10.10 Yosemite.
successfully checkout cart parameters
| Parameters | Value |
|---|---|
| processes | 4 |
| poolSize per process | 50 |
| type | linear |
| Resolution in milliseconds | 1000 |
| Iterations run | 25 |
| Number of users | 1000 |
| Number of tickets in each cart | 5 |
| Execution strategy | slicetime |
MMAP
The MMAP engine is run using the default settings on MongoDB 3.0.1.
successfully checkout cart
| Statistics | Value |
|---|---|
| Runtime | 124.397 seconds |
| Mean | 2292.494 milliseconds |
| Standard Deviation | 1527.972 milliseconds |
| 75 percentile | 2916.34 milliseconds |
| 95 percentile | 5211.314 milliseconds |
| 99 percentile | 7884.857 milliseconds |
| Minimum | 149.741 milliseconds |
| Maximum | 14779.998 milliseconds |
WiredTiger
The WiredTiger engine is run using the default settings on MongoDB 3.0.1.
successful cart checkout
| Statistics | Value |
|---|---|
| Runtime | 112.994 seconds |
| Mean | 2302.15 milliseconds |
| Standard Deviation | 2052.946 milliseconds |
| 75 percentile | 3145.921 milliseconds |
| 95 percentile | 6451.251 milliseconds |
| 99 percentile | 9608.811 milliseconds |
| Minimum | 19.081 milliseconds |
| Maximum | 17122.854 milliseconds |
This is an update heavy schema and this impacts WiredTiger more than MMAP, due to the need to rewrite the full document on each update when using WiredTiger. Also note that the total runtime is higher than the 25 seconds of load we apply, pointing to the fact that the system is overloaded for this hardware configuration.
Notes
The difference here is sizable between the WiredTiger and MMAP storage engines reflecting the cost of rewriting the documents in WiredTiger due to the lack of in place update support.