What to make the structure of the database with the work schedule of employees?

Question

PHP

Generally a subject. I can’t think of everything, how to build the correct structure of the employee base with their work schedule. Suppose each employee can specify from Mon to Sun the working time (from XX:XX to XX:XX) for each day of the week individually, and in the same way lunch is also individually for each day. It turns out that you need to make the structure such that later, you can easily make a request who can work from 13:00 to 16:00 on Tuesday, for example. The only thing that comes to mind is to simply create 14 fields, two fields for each day of the week FROM and TO, and enter the timestamp there from the beginning of the day, i.e. 00:00 i.e. if from 9:00 to 16:00 Monday then ~ mon_beg = 32400 & mon_end = 57600 and use WHERE to check the occurrence of the interval. But I have a feeling that this is somehow hemorrhoids and perhaps there are other ideas or already methods that are more competent for such cases.
I would be glad if anyone can give me some advice on this subject. Thank you!

Answer 1

I would do this:
The table contains 4 fields: user_id, day(Enum), from(int), to(int)
The user creates a field for each day of the week, in which he can specify FROM and TILL what time he works.
That is, it will turn out like this:
user_id | day | from | to
1 | 1 | 10 | 12
1 | 2 | 9 | 16
2 | 1 | 8 | 15 The advantage
is that you can create multiple lines for each day of the week:
user_id | day | from | to
1 | 1 | 10 | 12
1 | 1 | 14 | 20
This allows you to specify multiple intervals.
The selection is simple:
select * from days where day = {current_day} and from >= {from_hour} and to <= {to_hour}