CODEWITHSUNDEEP
phpmysqlcakephpdatabase-designschema
user140291
user140291

Reputation:

Schema advice for minibus booking system

I'm developing with CakePHP 2.0 and MySQL.

I'm trying to create a minibus booking solution but I'm unsure if I'm following the right approach.

We have one minibus which we can book out. I'm not bothered about booking times overlapping at this stage. I've made a table for the minibus properties but I need to define the relationship between the minibus and the passengers.

Each minibus can have many (16) passengers. A passenger can travel on more than one minibus (one today, one tomorrow etc).

Also I need to be able to set the type of passenger to either passenger or driver. Will this need three tables? I was thinking:

Buses table (id, description)
Users table (id, firstName)
Passengers table (buses.id, users.id, passenger_type)

Any advice would be appreciated.

Upvotes: 1

Views: 418

Answers (3)

Asdfg
Asdfg

Reputation: 12233

It will look something like this:

One table to store capacity of the minibus. (CapacityId)

One table to store passenger information. (PassengerId, Name, address etc)

One table to store Minibus information. (MiniBusId, CapacityId, Make, Model, Year, color etc..)

One table for BookingInformation(BookingId, date, time, FromDestination, ToDestination etc)

One table for TripSchedule(TripId, BookingId, MinibusId)(Only if you have multiple buses per booking else you can add MiniBusId to BookingInformation table and get rid of this table)

One Link table to store TripId and PassengerId. (If you have only one minibus per booking, add BookingId instead of TripId)

Upvotes: 0

Joe Stefanelli
Joe Stefanelli

Reputation: 135858

I think I would introduce a Trip table and assign the bus to an instance of a Trip. Then introduce a junction table to resolve the many-to-many relationship between Trips and Passengers.

enter image description here

Upvotes: 2

Brian Driscoll
Brian Driscoll

Reputation: 19635

Given that one minibus can service many passengers, and one passenger can ride on many minibus routes, then what you have is a many-to-many (m-to-n) relationship.

Therefore, you'll need three tables: one for Passenger, one for Minibus, and one that relates the first two via key associations:

Passenger_Minibus
------------------ 
PassengerID INT NOT NULL 
MinibusID INT NOT NULL

Upvotes: 0

Related Questions