Database design

Question

Database design

Good morning!

Prompt as it is better to design the table storing time of work of the company? With maximum functionality ... I

thought to make 6 columns

id
id_company
day - day of the week
time_start - start time
time_stop - end time
type - type (lunch or not)

sort by time when selecting and it seems that something should work out

Thank you.

Answer 1

id
id_company
time_start - start time
time_stop - end time
time_start_dinner - start time of lunch (break)
time_stop_dinner - end time of lunch
time_last - end time of the shortened day.
type - day type field.
The types are as follows:
- Weekend,
- Weekday with a break,
- Weekday without a break,
- Monday,
- ...
- Sunday.
— Shortened day
Something like this. For all occasions.

Answer 2

====intervals====
id
company_id: INT
start: DATETIME
end: DATETIME
type_id: Foreign key to events
====events====
id: PK
type_name: CHAR
is_work_time: BOOLEAN
… The
query would be something like this:
SELECT TIMEDIFF(end, start) as diff FROM intervals SORT BY diff;

Answer 3

What are you going to do with this data?
You need at least 2 lists - 'working days' + 'weekends' (or rather 'non-working' and mark lunch in this way), the time format is more convenient according to the crontab type (by field per time level), then queries like - which company works on another company's lunch time will be relatively simple.