Lets say you have a
Table table (master) and a
Bookings table (detail). Bookings have some attributes like
- Name of booker etc.
Lets further assume bookings can be grouped "by day" (the restaurant will probably have some regular closing hours, and bookings will be not allowed when they overlap those closing hours).
Then organize your transactions in a way so all changes to the set of booking records for a specific table and a specific day are done in one transaction, which does
If you get a collision, you need some strategy for handling it, like "first comes first" or something like that, but you should never run into a situation where there are any overlapping bookings in the database.
A variation of that strategy might work even when the restaurant has no closing hours. Loading always all bookings of a table and modifying them at once, in one transaction, could in theory solve the problem, but this becomes quickly impractical if you have always to write all bookings for the last 5 years for a table, for each slight change to the schedule. But by restricting changes only to future bookings, and forbidding any change to older bookings, this becomes a strategy of reading, modfiying and committing the whole future schedule at once. That should IMHO work, even when the bookings set of a table cannot be easily cut into independent subsets by day.