MySql电影预约系统设计 [英] MySql Movie Reservation System Design

问题描述

我有一个电影预订系统的以下伪MySQL：

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

表格电影：

    id int auto_increment not null,
    name varchar(100) not null,
    .....

Table MovieSched：

Table MovieSched:

    movie_id foreign key refers to Movie,
    sched_id int auto increment,
    date & time,
    max_size

Table MovieSchedSignUp：

Table MovieSchedSignUp:

    sched_id,
    user_id 

电影计划有一个max_size的用户可以注册。要注册一个用户，我在MovieSchedSignUp中插入一行。

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

问题是：如何在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.

最有效的方法是什么？

推荐答案

SQL的粗略思想： -

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
)

根据选择插入记录。选择返回2个固定值，如果预订次数小于最大预订数量，则从返回计划编号的子查询中进行选择。

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.

这篇关于MySql电影预约系统设计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！