Logic for Developing a Scheduling System

Hi, I’m working on the logic to develop a scheduling system in PHP, it consists of saving the available hours that a company can work in the bank,

For example, certain days of the week may or may not have free time, as well as certain days, these periods in each time range can have different amounts of customers who can schedule according to the amount of time.

For example, in company 1 = from Monday to Friday from 7:00 am to 10:00 pm there are closed Saturday and Sunday hours

For example in company 2 = It only serves Saturday and Sunday from 1:00 pm to 0:00 am and is closed on other days

At each of these times I have to save the amount of people that can be served, for example

Company 1 = Monday 7:00 am = 4 vacancies
Company 1 = Tuesday 18:00h = 2 vacancies
Company 1 = Friday 19:00h = 0 vacancies - unavailable

Company 2 = Saturday 5:00 pm = 0 vacancies - unavailable
Company 2 = Saturday 18:00h = 5 vacancies
Company 2 = Sunday 20:00h = 6 vacancies

Thinking in a way that I find unconventional and illogical, I would create a form with several and several fields every day of the week with all available times and quantities to be placed as in the attached image, and would save in json format in the database like this for example

Column: scheduleDaysWeeksTimes

{"1":{"ativo":"0","horarios":{"07:00":7,"07:30":0,"08:00":0,"08:30":0,"09:00":0,"09:30":0,"10:00":0,"10:30":0,"11:00":0,"11:30":0,"12:00":0,"12:30":0,"13:00":0,"13:30":0,"14:00":0,"14:30":0,"15:00":0,"15:30":0,"16:00":0,"16:30":0,"17:00":0,"17:30":0,"18:00":0,"18:30":0,"19:00":0,"19:30":0,"20:00":0,"20:30":0,"21:00":0,"21:30":0,"22:00":0,"22:30":0}},"2":{"ativo":"0","horarios":{"07:00":0,"07:30":0,"08:00":0,"08:30":0,"09:00":0,"09:30":0,"10:00":0,"10:30":0,"11:00":0,"11:30":0,"12:00":0,"12:30":0,"13:00":0,"13:30":0,"14:00":0,"14:30":0,"15:00":0,"15:30":0,"16:00":0,"16:30":0,"17:00":0,"17:30":0,"18:00":0,"18:30":0,"19:00":0,"19:30":0,"20:00":0,"20:30":0,"21:00":0,"21:30":0,"22:00":0,"22:30":0}},"3":{"ativo":"0","horarios":{"07:00":0,"07:30":0,"08:00":0,"08:30":0,"09:00":0,"09:30":0,"10:00":0,"10:30":0,"11:00":0,"11:30":0,"12:00":0,"12:30":0,"13:00":0,"13:30":0,"14:00":0,"14:30":0,"15:00":0,"15:30":0,"16:00":0,"16:30":0,"17:00":0,"17:30":0,"18:00":0,"18:30":0,"19:00":0,"19:30":0,"20:00":0,"20:30":0,"21:00":0,"21:30":0,"22:00":0,"22:30":0}},"4":{"ativo":"0","horarios":{"07:00":0,"07:30":0,"08:00":0,"08:30":0,"09:00":0,"09:30":0,"10:00":0,"10:30":0,"11:00":0,"11:30":0,"12:00":0,"12:30":0,"13:00":0,"13:30":0,"14:00":0,"14:30":0,"15:00":0,"15:30":0,"16:00":0,"16:30":0,"17:00":0,"17:30":0,"18:00":0,"18:30":0,"19:00":0,"19:30":0,"20:00":0,"20:30":0,"21:00":0,"21:30":0,"22:00":0,"22:30":0}},"5":{"ativo":"0","horarios":{"07:00":0,"07:30":0,"08:00":0,"08:30":0,"09:00":0,"09:30":0,"10:00":0,"10:30":0,"11:00":0,"11:30":0,"12:00":0,"12:30":0,"13:00":0,"13:30":0,"14:00":0,"14:30":0,"15:00":0,"15:30":0,"16:00":0,"16:30":0,"17:00":0,"17:30":0,"18:00":0,"18:30":0,"19:00":0,"19:30":0,"20:00":0,"20:30":0,"21:00":0,"21:30":0,"22:00":0,"22:30":0}},"6":{"ativo":"0","horarios":{"07:00":0,"07:30":0,"08:00":0,"08:30":0,"09:00":0,"09:30":0,"10:00":0,"10:30":0,"11:00":0,"11:30":0,"12:00":0,"12:30":0,"13:00":0,"13:30":0,"14:00":0,"14:30":0,"15:00":0,"15:30":0,"16:00":0,"16:30":0,"17:00":0,"17:30":0,"18:00":0,"18:30":0,"19:00":0,"19:30":0,"20:00":0,"20:30":0,"21:00":0,"21:30":0,"22:00":0,"22:30":0}},"7":{"ativo":"0","horarios":{"07:00":0,"07:30":0,"08:00":0,"08:30":0,"09:00":0,"09:30":0,"10:00":0,"10:30":0,"11:00":0,"11:30":0,"12:00":0,"12:30":0,"13:00":0,"13:30":0,"14:00":0,"14:30":0,"15:00":0,"15:30":0,"16:00":0,"16:30":0,"17:00":0,"17:30":0,"18:00":0,"18:30":0,"19:00":0,"19:30":0,"20:00":0,"20:30":0,"21:00":0,"21:30":0,"22:00":0,"22:30":0}}}

So in the case if 1 active means that Monday may have scheduled times, here comes the giant list of times, example 07:00 and then the number of people in case 7

I’m having trouble coming up with a simpler logic that works, If anyone has an idea and can help, an opinion on how to do it, I’m very grateful.

I’m afraid I don’t know json. Presumably you will be storing the data in a MySql database? It strikes me that perhaps you should come at the problem for the other direction and design the database first and only then think about how your php/html/js will interact with this?

What do you want to do with the data when it has been put into the database? When you know how many workers each company can provide and when?

1 Like

That seems like a good idea.

Why would you do that? It strikes me that would make it unnecessarily complicated to do pretty much anything with the data, though I must say I’m not that experienced in using JSON to store data. What happens when you want to find any slot that has a certain number of people available? You’ve gone from a simple query to a need to retrieve data and extract it within your code.

This might be better in the “Databases” section as I don’t really see anything specific to PHP here.