Database Design Help - Theatre Booking

[tjginge]

I need to build a database for a theatre booking system

Im not sure how many tables I will need to have for the seating etc.

The theatreSeating Numbers are as follows.
• 800 seats on two floors (500 on top, 300 on bottom, however without seats capacity is 750)

Seating Prices.

• Stalls D-H = £50
• Stalls K-X = £30
• Circle stage left or right = £50
• Circle A-E = £40
• Circle F-J = £30
• Standing tickets = £15

Currently run Two shows per day, morning and afternoon. I need to ensure that there are no double bookings.

Any help would be greatly appreciated.

Thankyou.

[BitBlt]

Interesting exercise. Can we assume that the actual seats are not individually numbered? It would of course make things a lot more precise, but standing-room tickets probably wouldn't have numbers...I can just see it now...

"You there...ticket 353! Your left foot is in the wrong place! You're infringing on ticket 354's right foot space!"

Probably not...

Anyway, here's a possible solution for you. It consists of three tables. First is for what I call a "SeatGroup". That is (for example) "Stall D", "Circle Stage Right", "Circle F" and the like. Next is a "ShowBooking" which corresponds to a single show on a single day either morning or afternoon. Last is a "ShowBookingSeatGroup" which shows how many tickets were sold for each Show in each SeatGroup. You'll have to write some code to update the count of seats so you don't double-book.

Since I don't know what DBMS you are using to store your database, here's some MS SQL code to get you started:

CREATE TABLE dbo.ShowBooking(
    ShowBookingId int IDENTITY(1,1) NOT NULL,
    ShowDate date NOT NULL,
    ShowTime char(2) NOT NULL,
    CONSTRAINT PK_ShowBooking PRIMARY KEY CLUSTERED 
        (
        ShowBookingId ASC
        )
)
GO
CREATE TABLE dbo.SeatGroup(
    SeatGroupId int IDENTITY(1,1) NOT NULL,
    SeatGroupName varchar(20) NOT NULL,
    SeatGroupFloor int NOT NULL,
    SeatGroupCount int NOT NULL,
    SeatGroupPrice money NOT NULL,
    CONSTRAINT PK_SeatGroup PRIMARY KEY NONCLUSTERED (SeatGroupId ASC)
)
GO
CREATE TABLE dbo.ShowBookingSeatGroup(
    ShowBookingId int NOT NULL,
    SeatGroupId int NOT NULL,
    SeatsBookedCount int NOT NULL,
    CONSTRAINT PK_ShowBookingSeatGroup PRIMARY KEY CLUSTERED (ShowBookingId ASC, SeatGroupId ASC),
    CONSTRAINT FK_ShowBookingSeatGroup_SeatGroup FOREIGN KEY(SeatGroupId) REFERENCES dbo.SeatGroup (SeatGroupId),
    CONSTRAINT FK_ShowBookingSeatGroup_ShowBooking FOREIGN KEY(ShowBookingId) REFERENCES dbo.ShowBooking (ShowBookingId)
)

As with any system that has multiple possible updates at once, you'll have to make sure that when you go to update the count, you have sufficient error trapping so you can handle situations where you have two people trying to get one slot.

Hope this helps! Good luck!