23feb26 - Bus ticket : Data model

Moving from a blank piece of paper (the ticket) to a fully functional database requires a structured pipeline. Here is the end-to-end process from the initial interview to the final implementation.

---

Phase 1: Requirement Gathering (Client Interview)

This is where you ask the structured questions we discussed. The goal is to identify Business Rules.

1. Fleet & Inventory

• How is the fleet of buses structured and categorized?

  • Do all buses have the same seating configuration, or do we need a separate Seat_Map for different layouts?

  • What is the process for taking a bus out of rotation for maintenance without breaking existing bookings?

2. Routes & Stations

• Are routes modeled as "Point-to-Point" or "Multi-Stop" journeys?

  • If there are mid-way stops, can a passenger book a specific segment (Stop B to Stop C) on a long-haul trip?

  • How do we handle seat inventory for those segments to prevent overbooking a specific leg of the journey?

3. Ticketing Lifecycle

• What is the full lifecycle of a ticket from purchase to travel?

  • What are the possible statuses (e.g., Confirmed, Checked-in, Cancelled)?

  • Does the ticket require a unique Hash or QR_Code for digital validation?

---

Phase 2: Conceptual Modeling (ER Diagram)

Before writing code, you visualize the entities and how they relate. This is usually done on a whiteboard or a tool like Lucidchart.

• Entities: User, Bus, Route, Trip, Booking.

• Key Relationship: A Trip is an instance of a Route at a specific time using a specific Bus.

• Cardinality: One Trip has many Bookings; One User can have many Bookings.

-------------------------

Conceptual & Collaborative Tools (The "Big Picture")

These are used for brainstorming and gathering requirements before a single line of code is written.

• Miro (Intelligent Whiteboarding)

  • Official Site: Miro Database Design Tool

  • Core Feature: An "infinite canvas" that now uses AI to help generate ER diagrams from text descriptions. It is strictly for collaboration between business users and developers.

• Lucidchart (Visual Diagramming)

  • Official Site: Lucidchart ERD Tool

  • Core Feature: Bridges the gap between a drawing and a technical tool; you can export your Lucidchart diagram as a SQL script to jumpstart your implementation

---

Phase 3: Logical Modeling (Schema Design)

Here, you define the actual table structures and data types.

Table	Primary Key	Foreign Keys	Key Attributes
Buses	bus_id	-	plate_number, capacity, model
Routes	route_id	-	origin_station, destination_station
Trips	trip_id	bus_id, route_id	departure_time, arrival_time, base_fare
Users	user_id	-	full_name, email, phone
Bookings	booking_id	trip_id, user_id	seat_number, final_price, status

---

Phase 4: Physical Implementation (SQL)

Now you write the DDL (Data Definition Language) to build the database.

SQL

-- Creating the Trips table as a central link
CREATE TABLE Trips (
    trip_id INT PRIMARY KEY AUTO_INCREMENT,
    bus_id INT,
    route_id INT,
    departure_datetime DATETIME NOT NULL,
    base_price DECIMAL(10, 2),
    FOREIGN KEY (bus_id) REFERENCES Buses(bus_id),
    FOREIGN KEY (route_id) REFERENCES Routes(route_id)
);

-- Creating the Bookings table
CREATE TABLE Bookings (
    booking_id UUID PRIMARY KEY,
    trip_id INT,
    user_id INT,
    seat_number VARCHAR(5),
    status ENUM('active', 'cancelled', 'completed') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (trip_id) REFERENCES Trips(trip_id),
    FOREIGN KEY (user_id) REFERENCES Users(user_id)
);

---

Phase 5: Optimization & Edge Cases

This final step ensures the system doesn't crash in the real world.

• Concurrency Control: Use Database Transactions (ACID) to ensure that if two people click "Buy" on the same seat at the same time, only one succeeds.

• Indexing: Add indexes on departure_datetime and route_id so that searching for available buses is lightning fast.

• Data Integrity: Use a "Check Constraint" to ensure seat_number doesn't exceed the bus_capacity.

Would you like me to explain how to handle the "Multi-stop" logic specifically in the SQL structure?