If the concern is manual maintenance of a large number of rows, you could solve this (potentially) by creating tables for each of your dimensions: weeks, time slots, and venues. The complete set of possible slots for booking would be the cross product of these three dimensions. Actual bookings would be another table with foreign keys pointing to all three of these dimension tables.
With this type of design, instead of maintaining 3 x 25 x 26 records you will maintain 3 + 25 + 26 records. Note that if you segregate day of week and time of day into two tables you can reduce the number of records to be maintained even further (3 + 5 + 5 + 26).
The problem with this approach is when (and if) you have an exception. This design assumes that there are no blackouts in your schedule. For example, what if you close a room for a couple of weeks to be renovated? One way to handle this issue is to create booking records that cover the blackouts. If you have enough exceptions, then managing them may be almost as bad as just using the brute force method.
The question I would seriously consider is whether or not generating the initial list of slots available for booking is really that big a deal. You could easily automate the process to generate your big pile of available slots. This is really just a single query.
You should use one table for this, unless you need more stringent constraints.
Each area has the same number of rows and the same number of seats per row.
Let's say there are 5 rows in every area, and 6 seats in every row. You'd want to use something along these lines.
create table seats (
area char(1) not null check (area in ('A', 'B', 'C', 'D')),
row integer not null check ( row between 1 and 5 ),
seat integer not null check ( seat between 1 and 6 ),
primary key (area, row, seat)
);
To select a single seat, put three values in the WHERE clause.
select *
from seats
where area = 'A' and
row = 1 and
seat = 2;
To use a table like this to model seat reservations, populate it with every possible area, row, and seat. Then set a foreign key reference to it.
create table reservations (
performance_time datetime not null,
party_name varchar(40) not null,
area char(1) not null,
row integer not null,
seat integer not null,
primary key (performance_time, party_name, area, row, seat),
foreign key (area, row, seat) references seats (area, row, seat)
);
You can see all the seating for a performance with this query.
select s.area, s.row, s.seat, r.performance_time, r.party_name
from seats s
left join reservations r
on r.area = s.area and
r.row = s.row and
r.seat = s.seat and
r.performance_time = '2013-04-30 08:00 pm'
And you can get all the available seats for a performance with something along these lines.
with seating as (
select s.area, s.row, s.seat, r.performance_time, r.party_name
from seats s
left join reservations r
on r.area = s.area and
r.row = s.row and
r.seat = s.seat and
r.performance_time = '2013-04-30 08:00 pm'
)
select *
from seating
where performance_time is null
By default, SQL Server will create a clustered index for a primary key constraint. You'll want to give careful thought to the order of columns in your primary key constraints, and consider adding other indexes as well. (Especially since your outputs will often need to be ordered by area, row, and seat.)
Best Answer
Because you're not telling us much of what you need, I'll guess for everything, and we'll make it moderately complex to simplify some of the possible questions.
First thing about MVCC is that in a highly concurrent system you want to avoid table locking. As a general rule, you can't tell what does not exist without locking the table for the transaction. That leaves you one option: don't rely on
INSERT
.I leave very little as an exercise for a real booking app here. We don't handle,
The key here is in the
UPDATE.
We lock only the rows forUPDATE
before the transaction starts. We can do this because we've inserted all seat-tickets for sale in the table,event_venue_seats
.Create a basic schema
Test Data
And now for the Booking Transaction
Now we have the eventid hard coded to one, you should set this to whatever event you want,
customerid
andtxnid
essentially make the seat reserved and tell you who did it. TheFOR UPDATE
is key. Those rows are locked during the update.Updates
For timed reservations
What you would do here is set the
booking.event_venue_seats.txnid
asThe second the user reserves the seet, the
UPDATE
puts in the txnid. Your transaction table looks something like this.Then in every minute you run
You can prompt the user to extend the timer when nearing expiration. Or, just let it delete the
txnid
and cascade down freeing up the seats.