Results 1 to 12 of 12
  1. #1
    Join Date
    Oct 2008
    Posts
    12

    Unanswered: Database structure for venue seating

    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

  2. #2
    Join Date
    Sep 2008
    Posts
    8
    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)
    );

  3. #3
    Join Date
    Oct 2008
    Posts
    12
    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)

  4. #4
    Join Date
    Sep 2008
    Posts
    8
    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) 
    );

  5. #5
    Join Date
    Oct 2008
    Posts
    12
    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!!

  6. #6
    Join Date
    Sep 2008
    Posts
    8
    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)
    );

  7. #7
    Join Date
    Oct 2008
    Posts
    12
    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?

  8. #8
    Join Date
    Oct 2008
    Posts
    12
    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?

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Oct 2008
    Posts
    12
    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?

  11. #11
    Join Date
    Oct 2008
    Posts
    12
    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?

  12. #12
    Join Date
    Oct 2008
    Posts
    12
    Quote Originally Posted by martingavin21
    Is there a mysql function for this?
    Can this be done in mysql or would it have to be done in something like php?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •