Best way to setup database for booking site?

Need help Database
1

I’ve had a few months away from Bubble and I need to dust off the cobwebs.

I’m setting up a booking style website.

  • Customers can book (and then pay)
  • Businesses can confirm (and take payment)

I’m mostly confident I can set this up, but where I’m hitting a mental obstacle right now is that the Businesses will serve multiple locations and have different days availability.

E.g. ‘Dave’s Waste Removals’ might only work in Area 1, 2 and 3.
‘Joe’s Waste Services’ might only work in Area 3, 4 and 5.

To complicate further, ‘Dave’s Waste Removals’ might only work in Area 1 on the 5th, 7th and 9th of January and Area 2 on the 6th, 8th and 10th January.
Whilst ‘Joe’s Waste Services’ might work Area 3, 4 and 5 on the 6th to 9th of January.

I’m hitting a mental wall when I’m trying to think of the best database setup for this.

I’m thinking the following:

- Business

  • Name
  • Location Coverage = List of location names
  • Available Days = List of Availability

- Location

  • Location names = Towns option set

- Availability

  • Dates = Dates option set

Option Sets
~ Towns
~ Dates
(Bold text is is the data type)

I’m not sure if Dates is the best option here.

Lastly, I want to really simplify it so that the Customer can:

  1. Click Dropdown 1 to select a location
  2. Click Dropdown 2 to select an available date

Note: The Customer doesn’t need to see the business name, but there is only going to be one business that serves a Location(s), so Dropdown 2 should only show Availability of the Business that serves the Location selected in Dropdown 1.

I hope this is clear enough to help me set the best foundation for the database for this idea.

Help is much appreciated!

2

Are you building for a client or yourself? Why the need for business to only work certain areas on certain dates?