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.