Data structure for movie ticketing system in MongoDB

Question

I am trying to make a booking system for a cinema with MongoDB.

I keep all the information about the movie in a model Movie, including title, description, actors, rating, director, etc.

However, a movie can be shown in the cinema in different rooms and at different dates, so I also have a model Showtime, where I have room, price, and date.

Finally, I also need a model Ticket with fields purchasedBy, purchasedAt, isRedeemed, etc.

However, I don't know how to link the models and extract the showtimes. I want a list on the frontpage with all the movies (with title, description and image), but I also want to show the date and price. The problem is that date and price can vary since each movie can have multiple (and different) dates and prices, so I just want to show the smallest price and soonest date.

At the moment, I have a schema that looks like something like

movieSchema = Schema({
  name: String,
  description: String,
  image: String,
  showtimes: [{ type: ObjectId, ref: 'Showtime' }]
});

I could get a date and price by just taking the first showtime in the array of showtimes

Movie.find().populate('showtimes').then(movies => {
  movies.forEach(movie => {
    console.log(movie.showtimes[0].date)
    console.log(movie.showtimes[0].price)
  });
});

However, I need to sort by the most recent date and/or the lowest price, so I am not sure if my data structure is appropriate for this purpose.

What would be ideal, would be to be able to do something like this:

Movie.find().sort('showtimes.date showtimes.price').populate('showtimes').then(movies => {
  ...
});

but since I am only storing the IDs of the showtimes in my showtimes field, this is not possible.

Alternatively, I could change the schema to

showtimeSchema = Schema({
  date: Date,
  price: Number
});

movieSchema = Schema({
  name: String,
  description: String,
  image: String,
  showtimes: [showtimeSchema]
});

so I don't have to use populate(). However, the problem is that when a customer buys a ticket, I need to refer to the showtime in the ticket object, so I need a model for showtimes on its own.

Edit

As mentioned in the comments, it might be clever to embed the documents directly in movieSchema. However, I don't know what my Ticket model should look like.

Right now it is something like

ticketSchema = Schema({
  showtime: { type: ObjectId, ref: 'Showtime' }
  purchasedAt: Date,
  purchasedBy: { type: ObjectId, ref: 'User' }
  isRedeemed: Boolean
})

So when I am printing the ticket, I have to do something like

Ticket.findById(ticketId).populate({
  path: 'Showtime',
  populate: {
    path: 'Movie'
  }
}).then(ticket => {
  console.log(ticket.date);
  console.log(ticket.event.name);
});

Answer 1

I would use your second schema; there's really no sense in creating a new model/collection for the showtimes since you won't be making transactions on the showtime, but on visitors, movies, and tickets. So that looks like this:

movieSchema = Schema({
  name: String,
  description: String,
  image: String,
  showtimes: [{
    date: Date,
    price: Number
  }]
});

Then, what you can do is sort by the min/max values of the array. So that would look something like this:

Movie.find().sort({'name.showtimes.date' : -1, price: 1})

This takes the latest showtimes for each movie and sorts by that time (as well as lowest price).

EDIT:

You could have a reference to the movie in the ticket, and store the showtime there as well:

ticketSchema = Schema({
  showtime: Date,
  purchasedAt: Date,
  purchasedBy: { type: ObjectId, ref: 'User' }
  isRedeemed: Boolean,
  movie: { type: ObjectId, ref: 'Movie' }
})

If you need more structure than that for whatever reason, I would look at using SQL instead. Nested populates (essentially SQL JOINs) are a maintenance/optimization nightmare, and RDBMS are more suited for data like that.

EDIT 2:

Ok, let's weigh our options here. You are right, in the event of a time/venue change, you would have to update all tickets. So storing the showtime separately gives us that benefit. On the other hand, this now adds a layer of complexity to virtually every single ticket you look up, not to mention the performance detriment and added server costs. Even if ticket/venue changes happen frequently, I'm almost positive that your ticket lookups are much more frequent.

That being said, I think a good approach here is to store an _id on the showtime subojects, and lookup your tickets that way:

showtimeSchema = Schema({
  date: Date,
  price: Number
});

movieSchema = Schema({
  name: String,
  description: String,
  image: String,
  // When you use a sub-schema like this, mongoose creates
  // an `_id` for your objects.
  showtimes: [showtimeSchema]
});

// Now you can search movies by showtime `_id`.
Movie.find({showtimes: 'some showtime id'}).exec()

You could go one step farther here and register a static on the Movie model for easy lookup by showtime _id:

Movie.findByShowtime('some showtime id').exec()

When you've fetched the movie, you can grab the showtime like this:

var st = movie.showtimes.id('some showtime id');

Further reading on subdocuments.