mysql项目案例电影,MySql电影预约系统设计

I have the following pseudo MySQL for a movie reservation system:

Table Movie:

id int auto_increment not null,

name varchar(100) not null,

.....

Table MovieSched:

movie_id foreign key refers to Movie,

sched_id int auto increment,

date & time,

max_size

Table MovieSchedSignUp:

sched_id,

user_id

Every movie schedule has a max_size of users who can sign up. To register a user, I insert a row in MovieSchedSignUp.

The problem is: How do I insert a row in MovieSchedSignUp while ensuring the schedule is not "overbooked" (sign ups <= max_size)

Specifically, to register a user for a schedule, what is the query I should use, and how do I check whether the schedule is full or not (so I can tell the user whether registration is successful)? Note that these requires some sort of atomic operation, a transaction perhaps.

What is the most efficient way to go about doing this?

解决方案

Rough idea for SQL:-

INSERT INTO Table MovieSchedSignUp (sched_id, user_id)

SELECT '$move_sched_id', '$user_id'

FROM

(

SELECT sched_id, COUNT(*) AS BookingCount

FROM MovieSchedSignUp

WHERE sched_id = '$move_sched_id'

GROUP BY sched_id

HAVING BookingCount < $max_size

)

Insert a record based on a select. The select returns 2 fixed values, and selects from a sub query that returns the schedule id if the number of bookings is less than the max number of bookings.