Question for database gurus:

I need to save the openig hours of my clients in a database. Each day should have different opening hours and also the possibility for 2 breaks during the opening hours. There should also be an option for different local holidays for each client.

How to I acomplish this in a clean/performant/scalable way? Thank you for your answers!

So you need (7 opening hours, 2 possible breaks, n possible holidays) for each client?

@BindView Exactly, thanks for the sumary! :-D

You could go
Table(day UNIQUE KEY, break0, break1)
Table(id UNIQUE KEY, holiday)
If you are sure there are only 2 breaks or

Table(day UNIQUE KEY, breaks FOREIGN KEY)
Table(breaks FOREIGN KEY, time)
Table(id UNIQUE KEY, holiday)
If you want it more scalable

@BindView It seams so simple when I read your answer... wow. Thank you very much for this quick reply. I'll start an upgrade on my brain and tomorrow I will understand your answer completly.

Thanks again and happy holidays from Switzerland!

@BindView you forgot the id of the customer unless op is planning to support exactly one business.

Also, your table will need open and close hours for the day of week.

And maybe you will need to support multiple locations in the future?

And your holidays. You should have some reasonable way of identifying the holidays every year that each customer has. Holidays do repeat every year, but I would advise against building a (customer, date, holiday) table.

Instead, it should be possible to store the definition of a holiday (e.g. yearly Jan 1st) and from that definition, you should be able to derive the dates.

Look at how a calendar application is implemented; most of your problems will be solved there

I would start of with defining a standard week/schedule period that should be used if no special date is found.

This could be table
Id
Dayofweek
Open
Close
Breakonetime
Breakonelenthinminutes
Breaktwotime
Breaktwominutes

Breaks could be lifted to separate table if there could be more or less.

Then a holiday table

Id
Holidayname
Open
Close
... same as normal

Then get a library to calculate all standard holidays (easter is magic) so you do not have to store those for all years.

Lastly a custom holiday table
Date
Holidayname

That should make it easy to look up a dates schedule

@nickhh @Voxera Thank you too for your help :D

@nickhh ah yeah i thought there would be separate dbs for companies, but yeah, makes more sense this way