How create a schema for booking system with availability / time slots

Question

I am working on a personal project and I am stuck on how to start implementing a calendar for a shop owner where he can list services and availability time. Meaning, say I am a shop owner/barber, and I offer many services and I work from 9 am to 5 pm with one hour break, with time slots. let's say a hair cut would take 15 mins (duration).

How should I go on implementing this? Is it good to prepopulate the database with events? meaning, allow the shop owner to create his calendar and time slots and inserted into the database and whenever a user wants to book, the system check for availability according to the date he picked. if there is an available free day in that particular month at that certain hour, we can allow him to book, else we show him that this date/day is full or booked or something like this. For argument sake, let's say I allow the user to prepopulate the database with list of dates, is this a good practice because if you have 11 slots/day for 7 days * 4 weeks it would a lot right?

if that is the case I can only allow him to prepopulate for 2 months or so.

If someone can offer me an advice where to start that would be great.

Thanks in advance

Answer 1

Your question is an open one (people here don't like it ! XD). There is no perfect answer, and it depends on the needs of your "barber". I see two very different ways of implementing it (but I am kind of a beginner too) :

1. Your suggestion = fixed time slots. Don't worry about prepopulating those time slots with "booked: false", since even a year full of them 24/7 is actually a tiny DB. For instance, I'm working on time slots of 1min with loads of data each minute (trading bot), and that is still a ~100MB DB for a whole year !
2. My suggestion = create a new DB document only when the barber actually books something. Big advantage : he can choose both starting and ending hour by the minute. Now the question is, how to prevent overbooking (it works on airplanes though, so why not !).

I can show you an idea of how I would start it :

const bookingSchema = mongoose.Schema({
    _id: mongoose.Schema.Types.ObjectId,
    startTime: {type:Number, required:true},
    endTime: {type:Number, required:true},
    clientName: {type:String, required:true}
});

const Booking = mongoose.model('Booking', bookingSchema);

I would store startTime and endTime as timestamps in milliseconds, but that is a matter of choice. You can go all the way down to storing Y / M / D / H / m in separate fields. I prefer the logic in my code rather than in my DB.

Now, when we add a new booking, we can use some date logic (easier with timestamps) to check if there is a conflict with a preexisting booking :

var newStartTime = someTimestamp;
var newEndTime = someOtherTimestamp;
var newClientName = 'John Doe';

var conflictingBookings = await Booking.find()
    .where('startTime').lt(newEndTime)
    .where('endTime').gt(newStartTime)
    .exec();

// This selects all preexisting bookings which have both :
// ---> startTime < newEndTime
// ---> endTime > newStartTime
// You can check yourself : I think it handles all possible overlaps !

// Now, conflictingBookings is an array containing all documents in conflict.
// You can tell your barber the problems :

if (conflictingBookings.length === 0) {
    // everything ok, you can book it
    // here you add the new booking to your DB...
} else {

    conflictingBookings.forEach( booking => {
        console.log(`There is already a booking from ${convertToString(booking.startTime)} to ${convertToString(booking.endTime)} with ${booking.clientName} !`);
    });

    // convertToString() being your custom function to convert timestamps to readable dates (not discussed here).
    // we log all conflicting preexisting bookings to the console

}

And that's a good start. I actually thank you, because I will soon be coding such a booking mini-app for my own business, and I will need that logic !

Yet, I must advise you to check if some easier visual apps suiting your needs already exist, because the hard part of your project is actually the visual User Interface, and I don't know how to do it. Maybe integrate your code/database with some Calendar API (Google Calendar or whatever).

Good luck with that, keep trying, and tell us what you decide to go for !

---

[EDIT: Implementing "time slots" if you really want them]

The idea of "time slots" is not a reality for any job that works by bookings/appointments of human time (not talking about booking places/rooms here). Because appointments never take the time they should, and because the worker knows better than anybody if he can add that new appointment between two other appointments...

But if you really want time slots, I would build two separate collections/databases.

The first DB contains the bookings, same as above.

The second DB contains the time slots, with each time slot being the smallest amount of time your worker will need for the precision of his schedule (maybe 5mn, 10mn, 15mn...). Those are the "atomic" time slots. Let's say 10mn for this example. Here is the DB schema :

const slotSchema = mongoose.Schema({
    _id: mongoose.Schema.Types.ObjectId,
    time: {type:Number, required:true, unique:true}, // starting timestamp of the slot
    booked: {type:Boolean, required:true}, // obvious !
    bookingId: {type:String} // if booked, the _id of the booking in the other DB
});

const Slot = mongoose.model('Slot', slotSchema);

Now, you can prepopulate that DB using for loops (not discussed here).

When the worker wants to add a new booking, he gives start and end time that you can convert into timestamps : startTime and endTime. You can now book the corresponding time slots :

// superBarber wants to book from 10:30am to 11:10am
var startTime = /*the timestamp for 10:30am that day*/
var endTime = /*the timestamp for 11:10am that day*/

// You can check availability as I showed above...
// You insert a new booking in the Booking collection :
var yourNewBooking = await Booking./*do your update...*/;

// And now you can update your time slots :

var slotsToBook = await Slot.find()
    .where('time').gte(startTime).lt(endTime)
    .exec();

// slotsToBook = [10:30slot, 10:40slot, 10:50slot, 11:00slot]

slotsToBook.forEach(async (slot) => {
    if (slot.booked) {
        console.log(`Damn, the slot at ${slot.time} is already booked`); 
    } else {
        await Slot.findByIdAndUpdate(
            slot._id,
            {$set: {
                booked: true, 
                bookingId: yourNewBooking._id
            }}
        );
        console.log(`Just booked the ${slot.time} slot !`);
    }
});

// Note : here I assume you're working inside async functions and you know how to handle callbacks / promises / await. Another thing entirely...

Hope it helps...