5 {{ upvoteCount | shortNum }}
5 {{ upvoteCount | shortNum }}

How would you design a table for the following scenario?

by iamscottj
3 replies
I basically want to create a table for a theater management project in which there is a table which has all the seats as per the row (a - j) and seat number (1 - 20). This table has 3 states, vacant booked and booking which depends upon the state of the selection of the customer. I also wish to identify each of the seat in an unique way. And yes the 3 states are date dependent. For example seat A1 can be booked on 3rd and might be free on 4th. I am quite confused how to design such table. Please put your views and ideas how I can create a database for such scenario?
#design #management #scenario #table #theater
  • Profile picture of the author awesometbn
    Originally Posted by iamscottj View Post

    I basically want to create a table for a theater management project in which there is a table which has all the seats as per the row (a - j) and seat number (1 - 20). This table has 3 states, vacant booked and booking which depends upon the state of the selection of the customer. I also wish to identify each of the seat in an unique way. And yes the 3 states are date dependent. For example seat A1 can be booked on 3rd and might be free on 4th. I am quite confused how to design such table. Please put your views and ideas how I can create a database for such scenario?
    Did some searching and found some possible inspiration . . .

    Detailed explanation of a simple seat reservation system using PHP and mySQL,
    PHP 101: A Simple Seat Reservation System

    Using WP and Eventbrite for ticket sales,
    Eventbrite plugs into Wordpress to enable ticket sales - The Future of Event Management

    Using WP and Flutter and TheaterCalculus as a CMS for your theater,
    Introducing: TheaterCalculus? | Theater For The Future

    But here are some of my thoughts to answer your question. Sounds like you will need a table that lists all possible seats with their row and seat number location. Next you will need a table that lists all possible conditions such as vacant, booked, booking. Next you will need a table for customer contact details. And finally you need to link the date and timestamp for each transaction.

    Those will be the source tables. On the ordering form, database queries will use these tables for lookups, and to create a new view that will be sent to the reporting screen for ticket printing, and updating the changing status of seats being reserved. This is very similar to other transaction based databases that are linked by primary key to each other (left join, one-to-many relationship, etc).

    The real question is how many people will be in the system at the same time? More than one person doing reservations will need the database records locked during updates, or at least dynamically change the seat status as soon as a seat is selected for potential sale. Lots to talk about here regarding documentation and flow charting of the entire process from customer calling in, selecting a seat, making payment, getting a receipt, and printing the final report for tickets.
    {{ DiscussionBoard.errors[2433808].message }}
  • Profile picture of the author iamscottj
    Awesome reply. Thanks for that. I am still confused what to do and what not. I dont want to lock the database because of the fact that I want multiple customers to book at once. There will be let us suppose 50 simultaneous connections. I know assuming such small number wont do anything good but still lets suppose. I want it to be live updations like if one clicks on the seat, it turns the status into booking. So the other person who is currently booking it will get to know that this seat is being booked and wont be able to click on it. I know how to implement this via code but all I need is a good table. I am doing all of this in ASP.NET and might use some amount of JQuery too.
    Yeah the flowchart which you mentioned is exactly what I documented it. I am still pondering over it. And the most striking feature is that I dont want to create login and password for it. Its just not viable to use such things. If the customer wants just a ticket to be booked, why would he consider sign up? I have done lots of study for the project and found that people dont like to sign up just to get one ticket. I think you will also agree with it.
    One more thing just came in my mind that how can I track for which date the seat is empty or occupied or is currently advance booked? I am in a big confusion... Thanks for the help anyway...
    Signature
    Watch TV Shows Online

    Its beggars pride that he is not a thief.
    {{ DiscussionBoard.errors[2436087].message }}
    • Profile picture of the author LynnM
      Hi, I showed this to a friend who does MySQL databases and this was his response. If you need any more details let me know. Thanks.


      Event Booking Database Considerations

      A well-designed database would not attempt to do everything in a single table. The best approach is to consider the entities involved in the system and map each one to a separate table. A suggested schema might look like this.

      Table.......................Data items
      Customer................ Name, address, phone no, login details
      Event.......................Description, Start date, end date
      Seat........................ Seat no, optional comment (‘behind pillar’)
      Booking.................. CustomerID, Event Date, Seat, status, date and time of booking


      No locking of tables is required other than the booking record currently being created/updated by the customer

      Once the customer selects an event and a date they will be invited to select a seat. Only those seats for which no booking exists for that date will be shown as available.

      Using clustered indexing within the database you can automatically ensure that any seat can only be booked once for a given date.

      I presume that somewhere within the application the customer will be required to pay. For this reason I suggest that the initial booking would have a status of ‘Provisional’ which would be updated to ‘Confirmed’ once payment has been made. You could have a routine that would delete all provisional bookings older than, say 48 hours.

      I’ll be happy to develop this further and provide you with the necessary SQL scripts to create this database in MySQL.
      {{ DiscussionBoard.errors[2498938].message }}

Trending Topics