Red Bus Schema Design

RedBusSchemaDesign.txt

Tables:
  1. Bus(bus_id -> PK, bus_no, reg_company_name, total_seats, available_seats, bus_type(AC, NONAC, SLEEPER)
  2. Route(route_id -> PK, source, destination)
  3. Bus_route(bus_route_id -> PK, bus_id -> FK referencing bus(bus-id) , route_id -> FK referencing route(route_id) , direction(UP, DOWN)
  4. Bus_seat(bus_seat_id -> PK, bus_id -> FK referencing bus(bus_id) , seat_number
  5. User(user_id -> PK, password, email, user_name)
  6. Booking(booking_id -> PK, user_id -> FK referencing user(user_id), bus_route_id -> FK referencing Bus_route(bus_route_id), booking_date
  7. Payment(payment_id -> PK, booking_id -> FK referencing Booking(booking_id), amount, payment_date, payment_method(CASH, CARD, UPI, NETBANKING), payment_status(PENDING, COMPLETED, FAILED)
  8. Passenger(passenger_id -> PK, booking_id -> FK referencing booking(booking_id), name, age, gender, bus_seat_id -> FK referencing Bus_seat(bus_seat_id)
  9. Travel(travel_id -> PK, passenger_id -> FK referencing passenger(passenger_id), traveled(boolean), travel_date, booking_id -> FK referencing booking(booking_id)
  10. Passenger_travel - Join table of travel and passenger containing Primary keys of both tables
  11. Booking_passenger - Join table of booking and passenger containing primary keys of both tables
  
Cardinalities :
  1. Bus to route -> many to many ( one bus can have many routes, and one route can belong to many buses)
  2. Bus to seat -> one to many ( one bus can have many seats, but a particular seat will be of a single bus)
  3. Booking to passenger -> many to many ( One booking or one ticket can have many passengers, and a passenger can have many bookings at a time)
  4. Booking to travel -> one to many (one booking(ticket) can have many travel entries, some passengers travel some don't , but one particular travel entry belongs to a single booking)