Database structure for venue seating

martingavin21 · #1 (**permalink**) 10-01-08, 14:48

Hi all,

I'm an absolute newbie on the site please be nice

I have a friend that has asked me to design a database for his small venue to cater for taking bookings for different seats in his venue. This type of project is completely new to me so therefore I am unsure how each seat should be stored properly. Well I suppose I know a very long winded version which would be a field for each seat and another field for that seat type which would correspond with a costing table. Surely this is not the way to tackle this as it would be extremely long winded as there would be numerous different seat types etc not too mention a ridicules number of fields!

Any help on this would be extremely welcomed as I have been mulling this over for quite sometime and no definite solution seems to be coming to me

M

BargainPredator · #2 (**permalink**) 10-01-08, 15:49

I will take for granted that all the seats of a table are of the same type.

So from wath you are telling me, I would do this.

Create a table for the seat Types :

Code:

CREATE TABLE SeatType(
    seatTypeID INT NOT NULL,
    ....
    PRIMARY KEY (seatTypeID)
);

Create a table for the tables where you can associate the seat type :

Code:

CREATE TABLE Table(
    tableID INT NOT NULL,
    seatTypeID INT NOT NULL,
    ....
    PRIMARY KEY (tableID)
);

Create a table for the seats where you can associate the table (and seat type by association) :

Code:

CREATE TABLE Seat(
    seatID INT NOT NULL,
    tableID INT NOT NULL,
    ....
    PRIMARY KEY (tableID)
);

Create a table to store the info of each booking (client info, date...) :

Code:

CREATE TABLE Booking(
    bookingID INT NOT NULL,
    ....
    PRIMARY KEY (bookingID)
);

Finaly a table to associate the booking with the seats reserved :

Code:

CREATE TABLE BookingSeat(
    bookingID INT NOT NULL,
    seatID INT NOT NULL,
    PRIMARY KEY (bookingID, seatID)
);

martingavin21 · #3 (**permalink**) 10-01-08, 17:16

Thanks for your reply! Sorry I thought I mentioned that, my mistake. Some seats will be of different type and will have different costs attached to them. There will be no tables at the venue, just rows of seats. For example if we have block A, block B and block C with 20 rows of 25 seats in each row. Some rows (depending on their location) will be more expensive than others. If you imagine a similar venue set up to my text diagram below, how do I go about storing individual seat information like available/unavailable and type/cost?

=======================

................Stage..................

Block A | Block B | Block C

=======================

Block A

Row A Seats 1 -15 (price €20)
Row B Seats 26 - 50 (price €15)
Row C Seats 51 - 75 (price €15)
Row D Seats 76 - 100 (price €15)

Block B

Row A Seats 1 -15 (price €20)
Row B Seats 26 - 50 (price €20)
Row C Seats 51 - 75 (price €15)
Row D Seats 76 - 100 (price €15)

Block C

Row A Seats 1 -15 (price €20)
Row B Seats 26 - 50 (price €15)
Row C Seats 51 - 75 (price €15)
Row D Seats 76 - 100 (price €15)

BargainPredator · #4 (**permalink**) 10-01-08, 19:43

These explanation makes it much more clearer and simplify the diagram.

How about this :

Code:

CREATE TABLE row(
    rowID INT NOT NULL,
    blockID CHAR NOT NULL,
    rowNo CHAR NOT NULL,
    price DOUBLE,
    ....
    PRIMARY KEY (rowID)
);

CREATE TABLE Seat(
    seatID INT NOT NULL,
    rowID INT NOT NULL,
    seatNo INT NOT NULL,
    ....
    PRIMARY KEY (seatID)
);

CREATE TABLE Booking(
    bookingID INT NOT NULL, 
    .... 
    PRIMARY KEY (bookingID) 
);

CREATE TABLE BookingSeat( 
    bookingID INT NOT NULL, 
    seatID INT NOT NULL, 
    PRIMARY KEY (bookingID, seatID) 
);

martingavin21 · #5 (**permalink**) 10-01-08, 20:24

Oh kool!! Well done!! Would I need a table and corresponding fields in other tables for the blocks? I used 3 in my example but there will be more.

Thanks again for your help!!

BargainPredator · #6 (**permalink**) 10-01-08, 20:51

Obvoulsy, if you need to keep more info on the block then just letter that designate, you can add this table :

Code:

CREATE TABLE Block(
    blockID CHAR NOT NULL,
    ....
    PRIMARY KEY (blockID)
);

martingavin21 · #7 (**permalink**) 10-06-08, 18:43

Kool!! That's simplified it a lot, thanks!!

So for example, if there are 1000 seats in the venue, for each event that happens do I need to add 1000 rows to the seats table with an event id to tie that batch of seats to that particular event? Or would there be a better way to do it as for each event there would be 1000 rows added to that table and as you can imagine after 1000 events there would be 1,000,000 rows which would surely slow the whole thing down?

martingavin21 · #8 (**permalink**) 10-13-08, 11:38

Anyone any ideas and the below?

Quote:

Originally Posted by martingavin21

So for example, if there are 1000 seats in the venue, for each event that happens do I need to add 1000 rows to the seats table with an event id to tie that batch of seats to that particular event? Or would there be a better way to do it as for each event there would be 1000 rows added to that table and as you can imagine after 1000 events there would be 1,000,000 rows which would surely slow the whole thing down?

healdem · #9 (**permalink**) 10-13-08, 12:14

why would it slow things down?
if the perfomance was degraded (and I doubt it) then you could archive old events or delete them).

if you sell a booking for a sepcific location (eg row/seat X), table A then you need some mechanism of identifying that that specific location has sold, or is available. if you don't care about the location then you don't need to store 1000 records per event, just that you have sold x seats to date..

I'd expect an association between an event and a seat/location, in say a table called event bookings..

you need to identify what seats are available. that is potentially a tricky problem... do you create a row in the event bookings table, do you need to identify reservations of possible sales.

if you have to allocate tickets to a specific location then personally I think you need to create a row for each seat and event intersection. and then chaneg that status as required. if the seat isnt' in the intersection table then its not released for the event. if it exists in the interection table then it can have one of several states
>free
>optionally reserved (ie a customer has indicated that they want that seat but the sale hasn't completed.. that could be because you have a reservation requirement or you need a mechanism to lock a seat whilst a (possible) customer completes the booking
>sold
..plus any other states that your business may require

martingavin21 · #10 (**permalink**) 10-13-08, 14:35

Thanks for the reply!

Quote:

Originally Posted by healdem

why would it slow things down?

To be perfectly honest I've never had a site that has had to query a DB table with near to the million plus that this one will. With that many records would it not slow down? If not, how may rows is a table likely to hold before it starts to slow down?

Each individual seat will need to be accountable for so it is looking like a row for each seat alright, so that row will contain seat number and status (as you were saying available, pending, sold). So doing it this way with a row for each seat, obviously I can call back all seats that are either available, pending or sold but how would I go about calling back availability for seats beside each other? for example 3 seats together or up as far as even 10 seats together? Is there a mysql function for this?

martingavin21 · #11 (**permalink**) 10-20-08, 18:25

Quote:

Originally Posted by martingavin21

So doing it this way with a row for each seat, obviously I can call back all seats that are either available, pending or sold but how would I go about calling back availability for seats beside each other? for example 3 seats together or up as far as even 10 seats together? Is there a mysql function for this?

Anyone any ideas on how to go about this?