{"id":1490,"date":"2025-01-20T12:56:25","date_gmt":"2025-01-20T12:56:25","guid":{"rendered":"https:\/\/www.cmsgalaxy.com\/blog\/?p=1490"},"modified":"2025-01-20T12:56:27","modified_gmt":"2025-01-20T12:56:27","slug":"low-level-design-of-partner-and-renter-relationship-of-vehicle-booking-software","status":"publish","type":"post","link":"https:\/\/www.cmsgalaxy.com\/blog\/low-level-design-of-partner-and-renter-relationship-of-vehicle-booking-software\/","title":{"rendered":"Low Level Design of Partner and Renter Relationship of Vehicle Booking Software"},"content":{"rendered":"\n

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

\n\n\n\n

Existing Schema Observations<\/strong><\/h3>\n\n\n\n
    \n
  1. addvechicles<\/code> Table<\/strong>:\n
      \n
    • Appears to manage vehicle listings with attributes like:\n
        \n
      • id<\/code>, vender_ID<\/code>, shop_id<\/code>, vehical_id<\/code>, brand<\/code>, model<\/code>, etc.<\/li>\n<\/ul>\n<\/li>\n\n\n\n
      • Likely used for Partners to list their vehicles.<\/li>\n<\/ul>\n<\/li>\n\n\n\n
      • Additional tables are likely present for users, bookings, or other entities, but they were not visible in the preview.<\/li>\n<\/ol>\n\n\n\n
        \n\n\n\n

        Recommended Database Schema for Approach-2<\/strong><\/h3>\n\n\n\n

        Core Tables<\/strong><\/h4>\n\n\n\n
          \n
        1. Users Table<\/strong>:\n
            \n
          • Centralized user information.<\/li>\n\n\n\n
          • Fields:\n
              \n
            • id<\/code> (Primary Key)<\/li>\n\n\n\n
            • name<\/code><\/li>\n\n\n\n
            • email<\/code> (Unique)<\/li>\n\n\n\n
            • mobile_number<\/code> (Unique)<\/li>\n\n\n\n
            • password<\/code><\/li>\n\n\n\n
            • created_at<\/code>, updated_at<\/code><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/li>\n\n\n\n
            • Roles Table<\/strong>:\n
                \n
              • Stores role definitions (e.g., Renter, Partner).<\/li>\n\n\n\n
              • Fields:\n
                  \n
                • id<\/code> (Primary Key)<\/li>\n\n\n\n
                • name<\/code> (e.g., ‘Renter’, ‘Partner’)<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/li>\n\n\n\n
                • UserRoles Table<\/strong> (Many-to-Many Relationship):\n
                    \n
                  • Links users with roles.<\/li>\n\n\n\n
                  • Fields:\n
                      \n
                    • id<\/code> (Primary Key)<\/li>\n\n\n\n
                    • user_id<\/code> (Foreign Key to Users<\/code>)<\/li>\n\n\n\n
                    • role_id<\/code> (Foreign Key to Roles<\/code>)<\/li>\n\n\n\n
                    • status<\/code> (e.g., Active\/Inactive)<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/li>\n<\/ol>\n\n\n\n

                      Additional Role-Specific Tables<\/strong><\/h4>\n\n\n\n
                        \n
                      1. Vehicles Table<\/strong>:\n
                          \n
                        • Tracks vehicles listed by Partners.<\/li>\n\n\n\n
                        • Fields:\n
                            \n
                          • id<\/code> (Primary Key)<\/li>\n\n\n\n
                          • user_id<\/code> (Foreign Key to Users<\/code>)<\/li>\n\n\n\n
                          • vehicle_name<\/code><\/li>\n\n\n\n
                          • brand<\/code>, model<\/code>, location<\/code>, etc.<\/li>\n\n\n\n
                          • is_available<\/code> (Boolean)<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/li>\n\n\n\n
                          • Bookings Table<\/strong>:\n
                              \n
                            • Tracks vehicle bookings by Renters.<\/li>\n\n\n\n
                            • Fields:\n
                                \n
                              • id<\/code> (Primary Key)<\/li>\n\n\n\n
                              • vehicle_id<\/code> (Foreign Key to Vehicles<\/code>)<\/li>\n\n\n\n
                              • renter_id<\/code> (Foreign Key to Users<\/code>)<\/li>\n\n\n\n
                              • partner_id<\/code> (Foreign Key to Users<\/code>)<\/li>\n\n\n\n
                              • start_date<\/code>, end_date<\/code><\/li>\n\n\n\n
                              • status<\/code> (e.g., Pending, Confirmed, Cancelled)<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/li>\n<\/ol>\n\n\n\n

                                Business Logic Integration<\/strong><\/h4>\n\n\n\n
                                  \n
                                1. Ensure Partners Can’t Book Their Own Vehicles<\/strong>:\n
                                    \n
                                  • In the Bookings<\/code> table, validate that the renter_id<\/code> and partner_id<\/code> are not the same during the booking process.<\/li>\n<\/ul>\n<\/li>\n\n\n\n
                                  • Role-Based Dashboards<\/strong>:\n
                                      \n
                                    • Dynamically show role-specific data:\n
                                        \n
                                      • Renter Dashboard<\/strong>: Booked vehicles, payment history, etc.<\/li>\n\n\n\n
                                      • Partner Dashboard<\/strong>: Listed vehicles, earnings, etc.<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/li>\n<\/ol>\n\n\n\n
                                        \n\n\n\n

                                        SQL Table Definitions<\/strong><\/h3>\n\n\n\n

                                        Here\u2019s an SQL implementation for the recommended schema:<\/p>\n\n\n\n

                                        -- Users Table\nCREATE TABLE Users (\n    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,\n    name VARCHAR(255) NOT NULL,\n    email VARCHAR(255) UNIQUE NOT NULL,\n    mobile_number VARCHAR(20) UNIQUE NOT NULL,\n    password VARCHAR(255) NOT NULL,\n    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP\n);\n\n-- Roles Table\nCREATE TABLE Roles (\n    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,\n    name VARCHAR(50) NOT NULL\n);\n\n-- UserRoles Table\nCREATE TABLE UserRoles (\n    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,\n    user_id BIGINT UNSIGNED NOT NULL,\n    role_id BIGINT UNSIGNED NOT NULL,\n    status ENUM('Active', 'Inactive') DEFAULT 'Active',\n    FOREIGN KEY (user_id) REFERENCES Users(id),\n    FOREIGN KEY (role_id) REFERENCES Roles(id)\n);\n\n-- Vehicles Table\nCREATE TABLE Vehicles (\n    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,\n    user_id BIGINT UNSIGNED NOT NULL,\n    vehicle_name VARCHAR(255) NOT NULL,\n    brand VARCHAR(255),\n    model VARCHAR(255),\n    location VARCHAR(255),\n    is_available BOOLEAN DEFAULT TRUE,\n    FOREIGN KEY (user_id) REFERENCES Users(id)\n);\n\n-- Bookings Table\nCREATE TABLE Bookings (\n    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,\n    vehicle_id BIGINT UNSIGNED NOT NULL,\n    renter_id BIGINT UNSIGNED NOT NULL,\n    partner_id BIGINT UNSIGNED NOT NULL,\n    start_date DATE NOT NULL,\n    end_date DATE NOT NULL,\n    status ENUM('Pending', 'Confirmed', 'Cancelled') DEFAULT 'Pending',\n    FOREIGN KEY (vehicle_id) REFERENCES Vehicles(id),\n    FOREIGN KEY (renter_id) REFERENCES Users(id),\n    FOREIGN KEY (partner_id) REFERENCES Users(id),\n    CHECK (renter_id != partner_id)\n);\n<\/code><\/pre>\n\n\n\n
                                        \n\n\n\n
                                        Advantages of the Recommended Design<\/strong><\/h3>\n\n\n\n
                                          \n
                                        1. Scalability<\/strong>:\n
                                            \n
                                          • Supports future roles by adding entries in the Roles<\/code> table without significant redesign.<\/li>\n<\/ul>\n<\/li>\n\n\n\n
                                          • Clean Separation<\/strong>:\n
                                              \n
                                            • Data for roles is logically separated using UserRoles<\/code>, while maintaining a unified user base.<\/li>\n<\/ul>\n<\/li>\n\n\n\n
                                            • Flexibility<\/strong>:\n
                                                \n
                                              • Role switching is seamless via the UserRoles<\/code> table.<\/li>\n<\/ul>\n<\/li>\n\n\n\n
                                              • Integrity<\/strong>:\n
                                                  \n
                                                • Business rules (e.g., Partners cannot book their own vehicles) are enforced directly in the schema.<\/li>\n<\/ul>\n<\/li>\n<\/ol>\n\n\n\n
                                                  \n\n\n\n
                                                  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!<\/p>\n","protected":false},"excerpt":{"rendered":"
                                                  Based on the partial schema loaded from the provided SQL file, we can analyze and enhance the database design for<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-1490","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/www.cmsgalaxy.com\/blog\/wp-json\/wp\/v2\/posts\/1490","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.cmsgalaxy.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.cmsgalaxy.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.cmsgalaxy.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.cmsgalaxy.com\/blog\/wp-json\/wp\/v2\/comments?post=1490"}],"version-history":[{"count":1,"href":"https:\/\/www.cmsgalaxy.com\/blog\/wp-json\/wp\/v2\/posts\/1490\/revisions"}],"predecessor-version":[{"id":1491,"href":"https:\/\/www.cmsgalaxy.com\/blog\/wp-json\/wp\/v2\/posts\/1490\/revisions\/1491"}],"wp:attachment":[{"href":"https:\/\/www.cmsgalaxy.com\/blog\/wp-json\/wp\/v2\/media?parent=1490"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.cmsgalaxy.com\/blog\/wp-json\/wp\/v2\/categories?post=1490"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.cmsgalaxy.com\/blog\/wp-json\/wp\/v2\/tags?post=1490"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}