0

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?

1
  • Please provide SHOW CREATE TABLE, it is more descriptive than DESCRIBE. – Rick James Aug 22 '16 at 18:48
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.