Uniqueness-like constraint but with limit greater than 1

Question

I have a bookings table where I currently have a unique constraint which prevents multiple bookings having the same start/end time.

mysql> desc booking;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(11)      | NO   | PRI | NULL    | auto_increment |
| customer_id | int(11)      | YES  | MUL | NULL    |                |
| start_time  | datetime     | NO   | MUL | NULL    |                |
| end_time    | datetime     | NO   |     | NULL    |                |
| created_at  | datetime     | NO   |     | NULL    |                |
| updated_at  | datetime     | NO   |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+

mysql> show indexes from booking;
+---------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name             | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| booking |          0 | PRIMARY              |            1 | id          | A         |          10 |     NULL | NULL   |      | BTREE      |         |               |
| booking |          0 | uniq_booking_idx     |            1 | start_time  | A         |          10 |     NULL | NULL   |      | BTREE      |         |               |
| booking |          0 | uniq_booking_idx     |            2 | end_time    | A         |          10 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

However, now our business constraint has changed and we want to be able to allow up to five bookings at the same time.

What is the best approach to this problem?

Answer 1

Not all things can be easily achieved in SQL. I think the best approach is to do the test in the application. In pseudo code:

START TRANSACTION;
$ct = SELECT COUNT(*) FROM booking WHERE ...
if ($ct >= 5)
    error to user
else
    INSERT ...
COMMIT;

OK, I guess this could be encapsulated in a Stored Procedure, plus error handling in your application.