Low Level Design of Partner and Renter Relationship of Vehicle Booking Software

Posted on by

Based on the partial schema loaded from the provided SQL file, we can analyze and enhance the database design for Approach-2, where a single user can have multiple roles (e.g., Renter and Partner). Let’s go step-by-step:

Existing Schema Observations

  1. addvechicles Table:
    • Appears to manage vehicle listings with attributes like:
      • id, vender_ID, shop_id, vehical_id, brand, model, etc.
    • Likely used for Partners to list their vehicles.
  2. Additional tables are likely present for users, bookings, or other entities, but they were not visible in the preview.

Recommended Database Schema for Approach-2

Core Tables

  1. Users Table:
    • Centralized user information.
    • Fields:
      • id (Primary Key)
      • name
      • email (Unique)
      • mobile_number (Unique)
      • password
      • created_at, updated_at
  2. Roles Table:
    • Stores role definitions (e.g., Renter, Partner).
    • Fields:
      • id (Primary Key)
      • name (e.g., ‘Renter’, ‘Partner’)
  3. UserRoles Table (Many-to-Many Relationship):
    • Links users with roles.
    • Fields:
      • id (Primary Key)
      • user_id (Foreign Key to Users)
      • role_id (Foreign Key to Roles)
      • status (e.g., Active/Inactive)

Additional Role-Specific Tables

  1. Vehicles Table:
    • Tracks vehicles listed by Partners.
    • Fields:
      • id (Primary Key)
      • user_id (Foreign Key to Users)
      • vehicle_name
      • brand, model, location, etc.
      • is_available (Boolean)
  2. Bookings Table:
    • Tracks vehicle bookings by Renters.
    • Fields:
      • id (Primary Key)
      • vehicle_id (Foreign Key to Vehicles)
      • renter_id (Foreign Key to Users)
      • partner_id (Foreign Key to Users)
      • start_date, end_date
      • status (e.g., Pending, Confirmed, Cancelled)

Business Logic Integration

  1. Ensure Partners Can’t Book Their Own Vehicles:
    • In the Bookings table, validate that the renter_id and partner_id are not the same during the booking process.
  2. Role-Based Dashboards:
    • Dynamically show role-specific data:
      • Renter Dashboard: Booked vehicles, payment history, etc.
      • Partner Dashboard: Listed vehicles, earnings, etc.

SQL Table Definitions

Here’s an SQL implementation for the recommended schema:

-- Users Table
CREATE TABLE Users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    mobile_number VARCHAR(20) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Roles Table
CREATE TABLE Roles (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL
);

-- UserRoles Table
CREATE TABLE UserRoles (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    role_id BIGINT UNSIGNED NOT NULL,
    status ENUM('Active', 'Inactive') DEFAULT 'Active',
    FOREIGN KEY (user_id) REFERENCES Users(id),
    FOREIGN KEY (role_id) REFERENCES Roles(id)
);

-- Vehicles Table
CREATE TABLE Vehicles (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    vehicle_name VARCHAR(255) NOT NULL,
    brand VARCHAR(255),
    model VARCHAR(255),
    location VARCHAR(255),
    is_available BOOLEAN DEFAULT TRUE,
    FOREIGN KEY (user_id) REFERENCES Users(id)
);

-- Bookings Table
CREATE TABLE Bookings (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    vehicle_id BIGINT UNSIGNED NOT NULL,
    renter_id BIGINT UNSIGNED NOT NULL,
    partner_id BIGINT UNSIGNED NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    status ENUM('Pending', 'Confirmed', 'Cancelled') DEFAULT 'Pending',
    FOREIGN KEY (vehicle_id) REFERENCES Vehicles(id),
    FOREIGN KEY (renter_id) REFERENCES Users(id),
    FOREIGN KEY (partner_id) REFERENCES Users(id),
    CHECK (renter_id != partner_id)
);

Advantages of the Recommended Design

  1. Scalability:
    • Supports future roles by adding entries in the Roles table without significant redesign.
  2. Clean Separation:
    • Data for roles is logically separated using UserRoles, while maintaining a unified user base.
  3. Flexibility:
    • Role switching is seamless via the UserRoles table.
  4. Integrity:
    • Business rules (e.g., Partners cannot book their own vehicles) are enforced directly in the schema.

Would you like help in migrating data from the existing schema to the new design or implementing this structure in your application? Let me know!