Seats Bus Booking PHP and MySQL

Question

I have another case.
I got the bus with 27 seats
then, if people pick the seat 4, so nobody can take that seat 4.

my question:

1. how design database contain 27 seats? I guess using looping until 27 with PHP
2. how to show in form, selection form contain the un-booked seat?
3. how to prevent if other people take same seat?

thanks.

Answer 1

Database: Have a BusType table with properties that would include the number of seats,cost,etc. Have a BusBookedSeats table that will hold a FK relationship to the Bus in question and keep a running total of the number of seats it has remaining and/or booked.

Form: Query the database and take the number of total seats - the number of booked seats. Add further validation to show the location of the seat.

Prevention: If the query returns a value greater than 0 (meaning taken in this instance) warn the user that it is taken and inform them to take another seat. Or don't even display the seat to them.

Answer 2

For your database:

1. You have a table with busses and available seat numbers
2. You have a table for reservations
3. You have a table which has a relation with a reservation on a specific seat on a specific bus

How to show the available seats:

1. Do a query for all reserved seats
2. Draw a picture of the bus and mark all reserved seats

How to prevent double reservation: You need some kind of transaction routine but it is hard to tell, when to lock a seat and when to unlock it (e.g. a user doesn't responds for some time). Usually you only reserve it at the last step. So making sure you have not too many steps or the selection is one of the last steps, you reduce the number of conflicts.

These are indeed only some very basic tips, but the whole problem is too complex to anwser it in short. If you have any specific questions you might want to aks them separately.

Answer 3

From a DB Perspective, based on what i understand of your requirments, i would suggest something on these lines :-

1. Bus Table - containing an entry for each individual bus. Has a column that indicates the seating capacity of this bus.

2. User Table - containing an entry for each user who can book a seat on a bus.

3. BusUser Table - transaction table containing a record for seat on a bus eg: if bus X has 27 = seating capacity, this table will have 27 records - each with the corresponding seat number associated with it.

From a UI perspective, you display the bus user contents. When a user selects one of the records on the UI, you associate the user with that record in BusUser table. BusUser - will have a column indicating the user associated with that bus instance.

Thats the general idea. Hope that gives some pointers for you to start off on..

Answer 4

Make sure you have some sort of a primary key which involves the time of the bus leaves etc along with the unique identifier for each bus (composite primary key). You can only have the unique identifier of the bus as that will limit each bus to be only ride once :)

The 27 seats of a bus i think should be handled by your application. Perhaps, insert 27 rows first for each bus and update when someone books a seat. This way, you can let the user perhaps select which seat they want as well. (Should be done from your GUI).

Of course you should have a status for each seat which you can use to find out whether it is reserved or not.

This is a VERY basic idea and I have not included any diagrams or anything of that sort. I hope I have not spoiled your opportunity to design the database on your own :)

Answer 5

A database lie In Sane has suggested is a good idea. Stopping duplicate bookings really isn't that difficult - you just need to choose a method.

1. Queue system - When people order you'd append them to the tail of a queue and then have a seperate script to pull people from the head. You'd then be able to evaluate each order at a steady pace, one by one.

2. Database level - If you're going to use MySQL you'd probabaly be better off using a engine like InnoDB which supports transactions (introduction: http://www.databasejournal.com/features/mysql/article.php/3382171/Transactions-in-MySQL.htm)