Key Components of the Database
- Users Table
- Drivers Table
- Rides Table
- Payments Table
- Location Table
- Surge Pricing Table
- Ratings and Feedback Table
1. Users Table
This table stores data about passengers (end-users) who request rides.
| Column Name | Data Type | Description |
|---|---|---|
| user_id | INT (PK) | Unique identifier for the user |
| name | VARCHAR | Full name of the user |
| VARCHAR | Email address for user communication | |
| phone_number | VARCHAR | Contact number of the user |
| created_at | DATETIME | Timestamp when the user registered |
| last_login | DATETIME | Timestamp when the user last logged in |
| profile_picture | VARCHAR | URL to the user’s profile picture |
2. Drivers Table
This table stores information about the drivers in the system.
| Column Name | Data Type | Description |
|---|---|---|
| driver_id | INT (PK) | Unique identifier for the driver |
| name | VARCHAR | Full name of the driver |
| VARCHAR | Email address of the driver | |
| phone_number | VARCHAR | Contact number of the driver |
| car_model | VARCHAR | Car model of the driver (e.g., Honda Civic) |
| car_number | VARCHAR | Car registration number |
| status | ENUM | Driver status (available, busy, offline) |
| rating | DECIMAL(3,2) | Average rating of the driver |
| last_login | DATETIME | Timestamp when the driver last logged in |
| created_at | DATETIME | Timestamp when the driver registered |
3. Rides Table
This table is the core of the Uber/Ola platform. It stores information about the ride request and its journey.
| Column Name | Data Type | Description |
|---|---|---|
| ride_id | INT (PK) | Unique identifier for the ride |
| user_id | INT (FK) | Foreign key to the Users table |
| driver_id | INT (FK) | Foreign key to the Drivers table |
| start_location | VARCHAR | The pickup location in latitude/longitude |
| end_location | VARCHAR | The drop-off location in latitude/longitude |
| start_time | DATETIME | Timestamp when the ride started |
| end_time | DATETIME | Timestamp when the ride ended |
| status | ENUM | Ride status (requested, accepted, completed, cancelled) |
| distance | DECIMAL(10,2) | Distance traveled in kilometers |
| fare | DECIMAL(10,2) | Fare for the ride |
| surge_multiplier | DECIMAL(3,2) | Surge pricing multiplier, if applicable |
4. Payments Table
This table stores data related to the payment for each ride.
| Column Name | Data Type | Description |
|---|---|---|
| payment_id | INT (PK) | Unique identifier for the payment |
| ride_id | INT (FK) | Foreign key to the Rides table |
| user_id | INT (FK) | Foreign key to the Users table |
| driver_id | INT (FK) | Foreign key to the Drivers table |
| payment_status | ENUM | Payment status (pending, completed, failed) |
| payment_method | ENUM | Payment method (card, wallet, etc.) |
| amount_paid | DECIMAL(10,2) | Amount paid for the ride |
| payment_date | DATETIME | Timestamp when the payment was made |
5. Location Table
The location table is crucial for real-time location tracking for both users and drivers. This table stores the most recent locations of users and drivers.
| Column Name | Data Type | Description |
|---|---|---|
| location_id | INT (PK) | Unique identifier for the location entry |
| user_id | INT (FK) | Foreign key to the Users table |
| driver_id | INT (FK) | Foreign key to the Drivers table |
| latitude | DECIMAL(9,6) | Latitude of the location |
| longitude | DECIMAL(9,6) | Longitude of the location |
| timestamp | DATETIME | Timestamp of when the location was updated |
6. Surge Pricing Table
This table stores information regarding surge pricing that is applied during periods of high demand.
| Column Name | Data Type | Description |
|---|---|---|
| surge_id | INT (PK) | Unique identifier for the surge pricing entry |
| location_id | INT (FK) | Location (pickup area) related to the surge |
| surge_multiplier | DECIMAL(3,2) | Surge pricing multiplier (e.g., 1.5x, 2x) |
| start_time | DATETIME | Time when surge pricing starts |
| end_time | DATETIME | Time when surge pricing ends |
7. Ratings and Feedback Table
After a ride is completed, both users and drivers can rate each other, providing feedback about the experience.
| Column Name | Data Type | Description |
|---|---|---|
| rating_id | INT (PK) | Unique identifier for the rating entry |
| ride_id | INT (FK) | Foreign key to the Rides table |
| user_id | INT (FK) | Foreign key to the Users table |
| driver_id | INT (FK) | Foreign key to the Drivers table |
| user_rating | DECIMAL(3,2) | Rating given by the user (1 to 5) |
| driver_rating | DECIMAL(3,2) | Rating given by the driver (1 to 5) |
| user_feedback | TEXT | Feedback from the user about the driver |
| driver_feedback | TEXT | Feedback from the driver about the user |
| timestamp | DATETIME | Timestamp when the rating was submitted |
Relationships & Foreign Keys
- Users Table: A user can request many rides (1-to-many relationship with the
Ridestable). They can also make multiple payments.
- Drivers Table: A driver can accept multiple rides, and each ride will have one associated driver.
- Rides Table: Each ride is linked to both a user and a driver (many-to-one relationship). Additionally, rides can have multiple ratings and payments.
- Payments Table: Payments are linked to a specific ride, user, and driver (many-to-one).
- Location Table: Real-time location updates are stored for both users and drivers, linked by their IDs.
- Surge Pricing Table: Surge pricing is applied dynamically based on location and time.
- Ratings and Feedback Table: Allows users and drivers to rate each other, linked to the rides they were involved in.
Indexes
- Primary Indexes: These are defined for each table’s
IDcolumns (e.g.,user_id,ride_id,payment_id).
- Secondary Indexes: Often created for frequently queried fields such as
ride status,driver status,location,surge pricing, andpayment statusto optimize read performance.
