Hey,
I’m currently building an app for my company where people can book a room for an amount of time ( hours usually ).
Actually I have a User collection with a document by user ( with id email etc )
workspace collection because people are inside workspace and they can’t book every room
A room collection ( with a document by room )
A day reservation with a document by day :
{
_id: objectId,
day: Date,
workspace: objectId
}
and a collection called Reservation represented that way :
{
_id: objectId,
dayReservationId: ObjectId with the id from dayReservation document for the day
dateIn: starting date of the reservation
dateOut: same for the end date
userId: objectId of the user who booked it
roomId: objectId of the booked room
}
Everytime people do a reservation it will create a document like that. Our backend is able to merge document reservation if someone had already 2 or 3 reservation for this room and he makes an other reservation that includes the others
I designed it this way with Prisma, at first i thought it will be ok to have a sort of relationnal DB schema, but, my company is a very big one with many rooms and they want to use this for all their office. So the reservation collection after a year can reach 10millions documents, i guess 10million can be ok but the project is expected to last more than a year.
My option is, keep this schema, after a month or more, run a cron job which will group reservation after actualMonth -2 or -3, by week that way every passed reservation will be grouped.
The project is built with nextjs and prisma, one of my coworker decided to do it in a special way, ex :
You want day reservation ?
=> get the dayReservationId, after that
=> get the reservation with the dayReservationId
He don’t want to use the “lookup” feature with prisma because it seems more expensive than using a bg query than an other one after it etc.
One more thing is we need to get some data insight about room occupancy, people reservation, etc.
Do you think designing something that way will be a good idea, ( i guess not ), if not can you help me with this one ?
I do like mongo but my experience with it is really small !
Thank you very much in advance,