It appears you are looking for a uniqueness constraint on a time range. In some DBMSs such as PostgreSQL you can declaratively declare constraints on ranges. In other DBMSs a declarative constraint is not possible and an imperative approach, such as using triggers is required.

In either case a correct implementation that considers multi-user concurrency will include locks to prevent double bookings. In this case, an exclusive lock on the "dinner table" record to which the booking applies could be taken before the check is made regarding overlaps to ensure only one booking is processed at anyone time.

However, in the general case there may not be a suitable existing record to lock to control concurrency. Also, locking parent records to control access to child records can be considered over-zealous as it needlessly serializes access to some resources when it is not required. Explicitly named locks may be used in these case, for instance a lock named "TABLE_BOOKING_[TABLE_ID]" using functionality such as advisory locks in PostgreSQL or DBMS_LOCK in Oracle.

For optimistic concurrency control there could be a booking version number on the dinner table "record" which gets incremented each time an associated booking is inserted or updated. You could use this when checking for overlaps in bookings to ensure no changes had been made between retrieving the existing booking data to check for overlaps and actually making the booking. A booking would not be allowed if any changes had been made between the retrieval and save and an error raised.

It appears you are looking for a uniqueness constraint on a time range. In some DBMSs such as PostgreSQL you can declaratively declare constraints on ranges. In other DBMSs a declarative constraint is not possible and an imperative approach, such as using triggers is required.

In either case a correct implementation that considers multi-user concurrency will include locks to prevent double bookings. In this case, an exclusive lock on the "dinner table" record to which the booking applies could be taken before the check is made regarding overlaps to ensure only one booking is processed at anyone time.

However, in the general case there may not be a suitable existing record to lock to control concurrency. Also, locking parent records to control access to child records can be considered over-zealous as it needlessly serializes access to some resources when it is not required. Explicitly named locks may be used in these case, for instance a lock named "TABLE_BOOKING_[TABLE_ID]" using functionality such as advisory locks in PostgreSQL or DBMS_LOCK in Oracle.

For optimistic concurrency control there could be a booking version number on the "dinner table" record which gets incremented each time an associated booking is inserted or updated. You could use this when checking for overlaps in bookings to ensure no changes had been made between retrieving the existing booking data to check for overlaps and actually making the booking. A booking would not be allowed if any changes had been made between the retrieval and save and an error raised.

It appears you are looking for a uniqueness constraint on a time range. In some DBMSs such as PostgreSQL you can declaratively declare constraints on ranges. In other DBMSs a declarative constraint is not possible and an imperative approach, such as using triggers is required.

In either case a correct implementation that considers multi-user concurrency will include locks to prevent double bookings. In this case, an exclusive lock on the "dinner table" record to which the booking applies could be taken before the check is made regarding overlaps to ensure only one booking is processed at anyone time.

However, in the general case there may not be a suitable existing record to lock to control concurrency. Also, locking parent records to control access to child records can be considered over-zealous as it needlessly serializes access to some resources when it is not required. Explicitly named locks may be used in these case, for instance a lock named "TABLE_BOOKING_[TABLE_ID]" using functionality such as advisory locks in PostgreSQL or DBMS_LOCK in Oracle.

It appears you are looking for a uniqueness constraint on a time range. In some DBMSs such as PostgreSQL you can declaratively declare constraints on ranges. In other DBMSs a declarative constraint is not possible and an imperative approach, such as using triggers is required.

In either case a correct implementation that considers multi-user concurrency will include locks to prevent double bookings. In this case, an exclusive lock on the "dinner table" record to which the booking applies could be taken before the check is made regarding overlaps to ensure only one booking is processed at anyone time.

However, in the general case there may not be a suitable existing record to lock to control concurrency. Also, locking parent records to control access to child records can be considered over-zealous as it needlessly serializes access to some resources when it is not required. Explicitly named locks may be used in these case, for instance a lock named "TABLE_BOOKING_[TABLE_ID]" using functionality such as advisory locks in PostgreSQL or DBMS_LOCK in Oracle.

For optimistic concurrency control there could be a booking version number on the dinner table "record" which gets incremented each time an associated booking is inserted or updated. You could use this when checking for overlaps in bookings to ensure no changes had been made between retrieving the existing booking data to check for overlaps and actually making the booking. A booking would not be allowed if any changes had been made between the retrieval and save and an error raised.