MySQL电影预订系统设计第2部分 [英] MySQL movie reservation system design part 2

问题描述

这与另一个问题类似，但不完全相同。

我的目标是设计一个电影预订系统。用户可以点击一个或多个空座位来预订电影时间表。但是他需要在15分钟前付款，否则预订的座位必须自动 自动为其他用户分配。

我有以下伪MySQL：

表电影：

 code> id int主键，
名称varchar，
 ..... 
  

表MovieSched：

  movie_id外键是指Movie，
 sched_id int主键，
 showtime datetime，//日程表的日期和时间
 count_signup int，//注册数量
 max_size int //最大座位数
  

表MovieSchedSignUp：

  sched_id外键指的是MovieSched， 
 user_id外键指用户，
 signup datetime //注册的日期时间
  

每个电影时间表都有一个 max_size 的用户可以注册。要注册用户，我会在MovieSchedSignUp中插入一行当前日期和时间。

一些限制自然会出现在需求中：

1. 由于接口和数据库之间可能不一致，当A尝试预订席位时，我需要通知用户A没有足够的席位。 （例如，另一个用户B可能购买了A之前的所有席位。）




2. 我需要 在MovieSchedSignUp中插入一行，同时确保计划不会超量预订（ count_signup < = max_size ）以及同时更新 count_signup 。




3. 我需要确保付款是在

我的想法是：

在后台运行批处理作业，以便在MovieSchedSignUp中删除过期行。

方式去做这个？任何其他想法？

解决方案

我认为在这种情况下，你可以使用一个批处理作业，

1. 开始交易


2. 插入记录添加到临时表中


3. 在临时表，MovieSched和MovieSchedSignUp之间进行连接，以
   检查记录数（组合临时和MovieSchedSignUp）不大于最大大小


4. 如果确定，则执行


5. 如果确定，它们提交事务，如果不是，则回滚
   事务。 li>
   

This is similar to another question but not entirely the same.

My aim is to design a movie reservation system. A user could click on one or more empty seats for a movie schedule to reserve them. But he needs to make a payment before 15mins is up, otherwise the reserved seats would have to be automatically given up for other users.

I have the following pseudo MySQL :

Table Movie:

    id        int primary key,
    name      varchar,
    .....

Table MovieSched:

    movie_id       foreign key refers to Movie,
    sched_id       int primary key,
    showtime       datetime,         // date and time of schedule
    count_signup   int,              // number of sign ups
    max_size       int               // max number of seats

Table MovieSchedSignUp:

    sched_id  foreign key refers to MovieSched,
    user_id   foreign key refers to User,
    signup    datetime              // datetime of signup

Every movie schedule has a max_size of users who can sign up. To register a user, I insert a row in MovieSchedSignUp with the current date and time.

A few constraints naturally arise from the requirements:

1. Due to possible inconsistency between the interface and database, I need to inform a user A when there are not enough seats available when A tries to reserve seats. (for e.g., another user B could have bought all the seats right before A.)

2. I need to atomically insert a row in MovieSchedSignUp while ensuring the schedule is not "overbooked" (count_signup <= max_size) as well as updating count_signup at the same time.

3. I need to ensure payment is made within 15mins, otherwise the reserved seats have to be freed.

My thoughts are:

1. Have extra columns in MovieSchedSignUp to keep track of when payment is made.

2. Use a transaction, but how do I return information about whether there are enough seats or not?

3. Have a batch job running in the background to delete the "expired" rows in MovieSchedSignUp.

What is the most efficient way to go about doing this? Any other thoughts? Don't really want to use a batch job, but is there any other way out?

解决方案

I think in this situation you are going to have to use a transaction.

1. Start the transaction
2. Insert the records to be added to a temp table
3. Do a join between the temp table, MovieSched and MovieSchedSignUp to check the number of records (combined temp and MovieSchedSignUp) isn't greater than max size.
4. If OK then do the insert
5. If OK them commit the transaction, if not then roll back the transaction.

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