How to structure a reservations system in a document based database

database-designdocument-databasesnosql

I really don't know if this is the right forum for this but I need help designing my database. Also I apologize for the horrible title: I don't know how to summarize the issue below in a single title.

I am building a reservation system where one reservation can have multiple services. Something like the following (single document):

{
    name: 'Gasim',
    start_date: '2015-07-24 1:30pm',
    services: [
      {member_id: 'member1', start_date: '2015-07-14 1:30pm', duration: '40 mins'},
      {member_id: 'member2', start_date: '2015-07-14 2:30pm', duration: '60 mins'}
    ]
}

I was going to go with this design but after having lots of debates with a number of people, I lost confidence in going with this design. I was told multiple times that, I should create a separate reservation for each service instead of aggregating services under a single reservation. Even though I like the idea that I have been proposed, I have one major problem with it and I would like you to shed a light on this issue for me.

All these services are being assigned in a single time period. So, let's say the example above, the whole time for a reservation is 100 minutes with 20 minute wait time = 2 hours.

If I separate them, that means I actually have two reservations. In a programming scenario this can be a good idea but let's look at it from a real world scenario. After I made a reservation, I come to the facility and say that I have a reservation under my name. Why would the front desk see two separate reservations under my name?

I am going to give some background information on how this thing become an issue. My system has the ability to query all the reservations, go through their services, and generate a list of available times based on the services that a user wants. I really expect this algorithm to be slow as I am going through lots of nested arrays and loops. However, this specific operation will be fetched at a very small rate even at large scales (I don't believe it will ever exceed 1000 requests/second, period).

Question

Is this design correct? Not performance-wise, just pure logic-wise. I don't want my design to be logically faulty just because it makes some operations fast. If this is not a correct design, can you please explain why?

Best Answer

get rid of the document-based DB, its a horrible system for the kind of query you'll be doing. Go back to boring old RDBMS and you'll be a lot happier.

If you do this, you can not only make reservations individually (which also help when someone adds a service to their reservation) but you can query all services assigned with a single reservation id. Simply and efficiently.

So I have a library with books. I can checkout a book and it writes an entry to the DB against that book with my userid to say who has it reserved, and can write the time I should return it. Next day I go back and check out another book, this writes an entry as before. The front desk, when looking at my user id can then see both books I have checked out because the query to return my details is a simple select * from reservations where user id = x.

You can extend this easily to include many reservations by including a table of reservations I make (linking my user id with the reservations) and then linking services within those reservations with the reservation id. Eg I have a separate account for books and for music at the library, each book gets associated with my 'book lending' account and not me - still works very easily with a traditional RDBMS.

Related Topic