March 25, 2011 at 5:32 am
Hi all,
I would like to make a web application similar to the typical application where you can buy cinema tickets.
In the main screen a users could select the day, the film room and the number of tickets that he want.
This is an application where at the same time can access over 2000 users and where everyone could buy tickets to see the same film, the same day and at the same time (so the system should have many controls because the film rooms have limited capacity; at the same time there are many cuestion to resolver such as table locks management ... ).
Could anyone tell me any idea about how i could implement a system like this? Anyone know if there are examples of Microsoft with a similar scenario as described?
Many thanks in advance.
March 25, 2011 at 5:46 am
SQL handles concurrency very well and automatically; it automatically locks a row of data for the milliseconds it takes to update the row, releasing the row as soon as the transaction is committed; other simultaneous connections waiting for that exact same row are forced to wait;
multiple thousand users on a web site is not the same as multiple thousand simultaneous SQL statements affecting the exact same row.
to avoid overselling, places like ticketmaster "reserves" your selection until final checkout (or a web timeout)
to do that, i'd assume you create a separate "Reserved" table, so the Unsold Tickets - Reserved = Available Tickets;
So the Reserved Table is used to hold ticket reservations until the final checkout completes.
Do you have any specific questions? a perfect example of Web + SQL might be this website, SQL Server Central; 1.4 million plus registered users, thousands connected at any one time, several posting to the exact same forum thread, and SQL handles them with no lockups ; over the years, as SQL got bigger, they threw more and more hardware into the mix, but it's handling everything beautifully.
Lowell
March 25, 2011 at 6:21 am
Hi Lowell,
Suppose you have the next scenario:
Suppose that you have web application with a main screen to buy cinema tickets. In this main screen you have to select the next information:
- User information (one or more)
- The day of the reserve.
- The Film.
- The cinema room.
After you select this information, you have a 'Send Information' button to buy tickets. This button have to do the next action:
1) Validated that the user who send the tickets buy request haven't any other request for the same day, film, room and cinema (and also of all the user that him has included in the main screen).
2) Insert the buy information in the 'Tickets Reserver' table.
3) Update the Cinema room capacity availability.
I havent very clear how do this 3 steps.
Including all of them in a stored procedure who doing all of this 3 steps? Including only the Insert statement in a stored procedure and defining validatin control in 'Tickets Reserver' table TRIGGER?
I would like to know how are tipically resolved this similar question.
All help is very appreciated.
Many thanks
March 25, 2011 at 7:25 am
ok, here's a crappy example schema i slapped together, just as an example.
if you step thru to the end, you'll see that the view at the end will always have "Available Tickets" correct, since it's a view that would get hit on demand.
I'd leave it o you till fill in the fuller, business like details( like showtimes per day, etc)
see if this helps you visualize the required structures.
CREATE TABLE TheaterRooms (
TheaterRoomID int identity(1,1) not null primary key,
SeatCapacity int)
--my two screen theater: a 500 seat and a 200 seat
INSERT INTO TheaterRooms
SELECT 500 UNION ALL SELECT 200
CREATE TABLE CurrentShows (
CurrentShowId int identity(1,1) not null primary key,
StartingPeriod datetime,
EndingPeriod datetime,
ShowName varchar(100) )
--my current movies, runs for 27 days(so i can release a new movie on friday 4 weeks from now.
INSERT INTO CurrentShows
SELECT getdate(),dateadd(dd,27,getdate()),'Terminator 4 Elimination'
CREATE TABLE ShowTimes(
ShowTimeID int identity(1,1) not null primary key,
ShowTimeDescrip varchar(30),
CurrentShowId int references CurrentShows(CurrentShowId),
TheaterRoomID int references TheaterRooms(TheaterRoomID),
SeatCapacity int )
--this movie's('Terminator 4 Elimination') run times in the 500 seat Theater 1
;WITH MyShowtimes AS
(
SELECT '10:00AM' AS st UNION ALL
SELECT '12:00PM' UNION ALL
SELECT ' 2:00PM' UNION ALL
SELECT ' 4:00PM' UNION ALL
SELECT ' 6:00PM' UNION ALL
SELECT ' 8:00PM' UNION ALL
SELECT '10:00PM'
)
INSERT INTO ShowTimes(ShowTimeDescrip,CurrentShowId,TheaterRoomID,SeatCapacity)
SELECT
MyShowimes.st,
CurrentShows.CurrentShowId,
TheaterRooms.TheaterRoomID,
TheaterRooms.SeatCapacity
FROM TheaterRooms
INNER JOIN CurrentShows ON CurrentShows.CurrentShowId = 1
CROSS JOIN MyShowtimes
WHERE TheaterRooms.TheaterRoomID = 1
SELECT * FROM ShowTimes
CREATE TABLE Reservations(
ReservationID int identity(1,1) not null primary key,
ShowTimeID int references ShowTimes(ShowTimeID),
CustomerID int ,--references Customers(CustomerID),
TicketsReserved int )
INSERT INTO Reservations
--create an example reservatino of 8 tickets for an imaginary customer
SELECT 1,1,8
GO
CREATE VIEW ShowsToSell
AS
SELECT
ShowTimes.* ,
AvailableTickets = (ShowTimes.SeatCapacity - Reservations.TicketsReserved)
FROM ShowTimes
INNER JOIN Reservations
ON ShowTimes.ShowTimeID = Reservations.ShowTimeID
GO
SELECT * FROM ShowsToSell
Lowell
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply