How Would You Do it? Build a Booking Calendar

[dhew]

Hi,

I’ve been working on this little challenge a few days and have gone through multiple concepts for how it works and drafts of the actual app, but am wondering if you’d share your thoughts and ideas. I’m sure there is a better, more efficient way to build the final product than what I’m currently doing, so please let me know if you have a better way for some or all of it!

I already have most of the backend built in Supabase, but everything is changeable and there are no bad ideas. I’m looking for advice, general approaches, examples, etc. more than specific problem solving at this point.

Basic concept:
Build a calendar-based ‘booking’ system to allow users to “book” one (of several) unique, physical assets for a set time block. In my specific case, users are booking boats, but there is nothing special about this (they could be cars or hotel rooms).

There are 9 boats currently. There are 3 time slots available each day for a boat to be booked. And the boats can be booked on any day of the week. In total, that is 189 individual booking ‘slots.’

There are a few complications I’m skipping over (boats can break and be taken ‘out of service’ at any point, some time slots are not available on some days, etc.).

The main issue I’m facing is how to build the front end, “booking” view, efficiently. Ideally users would see a ‘week view’ grid with 3 big rows, one for each time slot (9am - noon, noon - 4pm, 4pm - 8pm), with smaller rows nested inside for each boat, then columns for each of the week days:

But I’m struggling to build this in weweb. I have done 3 versions (using both the built in calendar element and hand-built grids), but am struggling with:

1. Stitching the various data pieces together (without using ~200 lookups/filterbykey/etc.)
2. Finding the right balance of flexibility with the data structure (to allow an admin to change the number and/or availability of boats, to add one-off “blackout” days, etc.).

I even considered (read: built) a massive workflow to just iterate through all the variations to create empty placeholder ‘bookings’ users could just ‘transfer’ into their name. But I stopped when I realized how fast that would escalate into (tens of) thousands of database objects, most of which would likely never be used!

---

Hoping someone might have some ideas or tips on how to combine these four different pieces of data (an individual booking, consisting of a boat, on a specific day of week, and within one of 3 pre-set times of day).

[Agustin_Carozo]

Hi Daniel, this is amazing!

I love how you are trying to figure this out.

I don’t have a specific tip for you right now, but would you consider creating a short video walking us through how you managed to create this structure?

I think it could be really helpful for others trying to achieve your same results.

Cheers!

[Broberto]

Hi, can you post your DB schema? I talk about not using lookups in the following post.

Become a Supabase Magician in WeWeb (Tutorial With Examples) – Broberto

[dhew]

Hi,

Sure. See below. Please keep in mind nothing here is totally fixed/I tried to design it with flexibility in mind for the future.

Perhaps the only thing of note here is I thought using a join table for boats + schedules (i.e., when ‘boats’ can be ‘booked’) would be the most flexible in the long run, to allow the Admin to change boat availability more easily (see note at bottom).

Also, I’ve read that post of yours a couple times now–thank you for writing it! I’ve implemented some of your tips already (such as using the ‘advanced’ field of collections) and they were very helpful.

Note from above:
I’ve read that calendar systems should store repeating events as single DB entries, then use an ‘exception’ event type to ‘override’ a specific occurrence of a recurring event, should the user need to cancel one instance but not the whole series of the recurring event. I tried to apply that same logic with this ‘schedule’ design (so admin could void a single schedule day/time without changing the long-term availability schedule), but TBD if that is a good idea (and how exactly it would work here).

[dhew]

Hi,

Sure. I am not really setup to do videos, but happy to share screenshots. Here are two showing a version of the table that does not work (yet).

This is just a static table/grid, with the exception of the boat names for the rows–those come from the actual ‘boat’ collection.

Perhaps this better illustrates the challenge. Ideally all the cells (“This is some text”) would always be physically present (and ordered) but could use logic to say:

• On user click of any cell: 'if ‘booking’ exists for boat.id at TIME on DATE, open ‘booking’ in modal, else create ‘booking’…then open it in modal
• Or: ‘if no ‘booking’ exists for boat.id at 9am on Sunday (4-27-2025), bgcolor = green’ (etc.)

---

And here is an earlier version that works, but requires creating the actual ‘bookings’ (database objects) as placeholders. Which leads to thousands of database items just to build the UI view.

From inside out:

• Individual “Cells” use a filterbykey on the ‘booking’ collection to return just those with the parent row’s boat.id, then filter those again to only show bookings that have a ‘start date’ that match a front-end variable controlling the calendar start date (adjusted for whatever ‘day of week’ column the cell is in)
• The cells are in a grid container, “Columns”, with 7 columns (one for each day of week)
• The grid container is nested in a few other containers for display text, but the next important two are “Row”, which is the ‘repeating item’ of “Collection List”. Collection List is just the ‘boats’ collection

[Broberto]

Here is the corrected link, the preview is broken for some reason.

Hi, I’ve made a simple POC in a few minutes. Check it out. Unfortunately I had only so much time to work on this but this should give you a direction. If you’d like to talk this through a little more in depth, or have some other questions regarding this, I do 1:1 consulting, which people use for these exact things.

[dhew]

Hi.

Very impressive that you built that out so quick! Thank you.

I don’t know much SQL and had not really thought about doing this as a postgres function, but it makes sense that could be the most flexible on the data side.

I’ll take some time to process this. On my first watch this morning though, it looked like the individual cells in the table are ‘time slots’ and you generate a single time ‘slot’ for each potential ‘booking’ (which is just a combination of a specific day of the week and a specific time of the day). So this would be 9 boats * 7 days of week * 3 time slots per day = 189 items in the “time” table. Is that correct?

I’ll experiment with this idea tomorrow–maybe my original attempt at similar logic (which also included start and end dates, drastically increasing the number of ‘slots’ needed to be generated) was simply too broad. And instead of each cell looking for an existing, ephemeral ‘booking,’ I should have them looking for a specific, permanent time slot data item.

That approach makes sense–now lets see if I can piece it together.

---

PS: I don’t seem able to rewatch the video currently. Is it possible it expired?

[Tae]

Why am I unable to watch the video?

[Broberto]

@dhew

PS: I don’t seem able to rewatch the video currently. Is it possible it expired?

Hi, I fixed it in the previous comment. As for the amount of times, you of course can make a Many to Many relationship, it really is up to your design, as I said, my design is by no means good, it’s just a POC to show how it would work. The implementation itself would require more than 20 minutes of throwing this together :d

@dhew

I don’t know much SQL and had not really thought about doing this as a postgres function, but it makes sense that could be the most flexible on the data side.

This is the number 0 mistake many people do when they come to me to learn, they don’t leverage the Supabase’s backend enough. They just glue everything together with lookups, which sometimes works, but not always. So you definitely asked the right questions above.

[dhew]

Partial update, as I think I’m narrowing in on a good solution.

I spent a lot of time considering the database side of the equation and working up various tweaks or alternatives to my schema.

A ‘lightbulb’ moment for me came after working through some of what @broberto shared yesterday (thanks again). It occurred to me that I was trying to solve a multi-variable problem and the easiest solution was to probably just eliminate variables.

Which got me to thinking hard about what a ‘cell’ in a the calendar table really is. It is not a 'space for an event (or “booking”)–a calendar space is a defined place in time. A thing ‘in and of’ itself. And in my specific case, each cell is not just a representation of a specific ‘time’, but also a specific time with a specific asset (a boat).

Which is exactly what a join table is.

I already had a boat_schedule join table and just needed to add/move a few fields around various tables to make it serve as the core of the calendar grid. The repeating cells are from this join table, which associates every boat to the specific schedules they must follow…and those schedules include the specific ‘days of week’ and ‘time of day’ they can be used.

And because of all the relations in the database schema (and the advanced query collection setup in WeWeb), I can ‘filterbykey’ my way to all the relevant data needed for the calendar display. I have yet to discover what the performance impact of this might be, but perhaps at the point that is noticable I could dig deeper on backend supabase functions or scripts.

More work to be done, but the tunnel appears to be getting brighter.