================================================================================ FLEX MARKETPLACE - COMPLETE DATABASE STRUCTURE ================================================================================ Database Name: flex_market Framework: CodeIgniter 4 Total Tables: 21 Total Foreign Keys: 34 Created: January 10, 2026 ================================================================================ Sr No | Table Name | Description | Related To Table | Foreign Key | Used For Dynamic Values? | Description of Dynamic Value ------|----------------------------|------------------------------------------------------|-------------------------------------|------------------------------------------------|--------------------------|-------------------------------------------------- 1 | users | Core table - All users (buyer, seller, delivery, admin, super_admin) | All tables | None (Root table) | yes | All user roles with reliability scores 2 | brands | Product brands created by sellers | Seller, Products | seller_id → users(id) | yes | Seller can create brand name, logo, description 3 | products | All product listings (sell/rent) | Seller, Brands, Offers, Orders | seller_id → users(id), brand_id → brands(id) | yes | Seller stores product data with pricing calculations 4 | product_images | Multiple images for each product | Products | product_id → products(id) | yes | Image gallery with primary image flag 5 | offers | Buyer offers for products (buy/rent) | Seller, Buyer, Products | product_id → products(id), buyer_id → users(id), seller_id → users(id) | yes | Show all offers with date ranges, rejection limits 6 | orders | Confirmed orders from accepted offers | User, Product, Delivery | buyer_id → users(id), seller_id → users(id), product_id → products(id), delivery_person_id → users(id) | yes | Order management with rental dates, extensions 7 | order_status_history | Audit trail for order status changes | Orders, Users | order_id → orders(id), updated_by → users(id) | yes | Track all status changes with timestamps 8 | transactions | Payment transactions for orders | Orders, Users | order_id → orders(id), user_id → users(id) | yes | Transaction history (credit/debit) 9 | reviews | Buyer/Seller reviews after order completion | Orders, Users | order_id → orders(id), reviewer_id → users(id), reviewed_id → users(id) | yes | Review system with self-review limits 10 | notifications | User notifications | Seller, Buyer, Delivery | user_id → users(id) | yes | All notifications show with action URLs, icons 11 | delivery_persons | Delivery person additional details | Delivery Users | user_id → users(id) | yes | Vehicle details, BGV status, delivery counts 12 | admin_settings | Admin configuration settings | Super Admin | None | yes | Formula percentages, limits, configurable values 13 | allowed_locations | PIN codes/areas where service is available | Admin | None | yes | Active locations for service coverage 14 | subscription_packages | Buyer subscription packages (3/5/7/10 contacts) | Buyers | None | yes | Package pricing with duration types 15 | user_subscriptions | Track active subscriptions for buyers | Buyers, Packages | user_id → users(id), package_id → subscription_packages(id) | yes | Subscription tracking with contact limits 16 | contact_views | Track when buyers view seller contacts | Buyer, Seller, Products, Subscriptions | user_id → users(id), seller_id → users(id), product_id → products(id), subscription_id → user_subscriptions(id) | yes | Enforce 3/5/7/10 contact viewing limits 17 | bgv_records | Background verification for delivery persons | Delivery, Admin | user_id → users(id), verified_by → users(id) | yes | KYC compliance - PAN, Aadhar, BGV status 18 | categories | Hierarchical taxonomy (Listing Type → Product Type → Category → Sub-Category) | Super Admin, Products | parent_id → categories(id), created_by → users(id) | yes | Dynamic cascading dropdowns managed by super admin 19 | seller_payment_history | Track seller payment events | Seller, Orders | seller_id → users(id), order_id → orders(id) | yes | Payment sent, rent sent, deposit sent tracking 20 | buyer_payment_history | Track buyer payment events | Buyer, Orders | buyer_id → users(id), order_id → orders(id) | yes | Payment deducted, rent deducted, deposit deducted 21 | migrations | System table for migration tracking | CodeIgniter System | None | no | CI4 migration version control ================================================================================ DETAILED TABLE STRUCTURES ================================================================================ ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ TABLE 1: users (Root Table - All User Roles) ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ Purpose: Store all user data for buyers, sellers, delivery persons, admin, super admin Related Tables: All tables (root of entire system) Foreign Keys: None (this is the root table) Columns (23 total): - id (INT, PRIMARY KEY, AUTO_INCREMENT) - name (VARCHAR 255) - email (VARCHAR 255, UNIQUE) - password (VARCHAR 255, hashed) - mobile (VARCHAR 15) - address (TEXT) - pin_code (VARCHAR 10) - user_type (ENUM: seller, buyer, admin, delivery, super_admin) - otp (VARCHAR 6) - otp_expires_at (DATETIME) - is_verified (TINYINT, DEFAULT 0) - seller_reliability_score (DECIMAL 3,2, DEFAULT 0.00) - buyer_rent_reliability_score (DECIMAL 3,2, DEFAULT 0.00) ⭐ NEW - buyer_buy_reliability_score (DECIMAL 3,2, DEFAULT 0.00) ⭐ NEW - buyer_overall_reliability_score (DECIMAL 3,2, DEFAULT 0.00) ⭐ NEW - seller_rent_reliability_score (DECIMAL 3,2, DEFAULT 0.00) ⭐ NEW - seller_sell_reliability_score (DECIMAL 3,2, DEFAULT 0.00) ⭐ NEW - delivery_rental_reliability_score (DECIMAL 3,2, DEFAULT 0.00) ⭐ NEW - delivery_sale_reliability_score (DECIMAL 3,2, DEFAULT 0.00) ⭐ NEW - is_subscribed (TINYINT, DEFAULT 0) - subscription_expires_at (DATETIME) - created_at (DATETIME) - updated_at (DATETIME) Dynamic Values: User roles, reliability scores auto-update based on reviews ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ TABLE 2: brands ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ Purpose: Store product brands created by sellers Related Tables: Seller (users), Products Foreign Keys: seller_id → users(id) CASCADE Columns: - id (INT, PRIMARY KEY, AUTO_INCREMENT) - seller_id (INT, FK to users) - brand_name (VARCHAR 100) - logo (VARCHAR 255) - description (TEXT) - created_at (DATETIME) - updated_at (DATETIME) Dynamic Values: Seller can create/manage brands, auto-tagged to products ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ TABLE 3: products ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ Purpose: Store all product listings (sell/rent) Related Tables: Seller (users), Brands, Product Images, Offers, Orders Foreign Keys: seller_id → users(id), brand_id → brands(id) Columns (30 total): - id (INT, PRIMARY KEY, AUTO_INCREMENT) - seller_id (INT, FK to users) - brand_id (INT, FK to brands, NULL) - listing_type (ENUM: sell, rent) - listing_type_category (VARCHAR 100) ⭐ NEW - First level cascading dropdown - title (VARCHAR 255) - description (TEXT) - category (VARCHAR 100) - sub_category (VARCHAR 100) - product_type (VARCHAR 100) - gender (VARCHAR 20) - size (VARCHAR 50) - color (VARCHAR 50) - price (DECIMAL 10,2) - rental_cost (DECIMAL 10,2) - rental_deposit (DECIMAL 10,2) - fitting_charge (DECIMAL 10,2) - has_bill (TINYINT, DEFAULT 0) - bill_image (VARCHAR 255) - original_purchase_price (DECIMAL 10,2) ⭐ NEW - For auto-calculations - suggested_sale_price (DECIMAL 10,2) ⭐ NEW - System calculated - suggested_rent_price (DECIMAL 10,2) ⭐ NEW - System calculated - suggested_deposit (DECIMAL 10,2) ⭐ NEW - System calculated - used_times (INT, DEFAULT 0) - dispatch_address (TEXT) - dispatch_pin_code (VARCHAR 10) - status (ENUM: pending, approved, rejected, sold, rented, inactive) - admin_remarks (TEXT) - views_count (INT, DEFAULT 0) - created_at (DATETIME) - updated_at (DATETIME) Dynamic Values: - Suggested prices auto-calculated based on original_purchase_price - Status updated by admin/system - Views count auto-increments ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ TABLE 4: product_images ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ Purpose: Store multiple images for each product Related Tables: Products Foreign Keys: product_id → products(id) CASCADE Columns: - id (INT, PRIMARY KEY, AUTO_INCREMENT) - product_id (INT, FK to products) - image_path (VARCHAR 255) - is_primary (TINYINT, DEFAULT 0) - display_order (INT, DEFAULT 0) - created_at (DATETIME) Dynamic Values: Primary image auto-selected for thumbnails, ordered display ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ TABLE 5: offers ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ Purpose: Store buyer offers for products (buy/rent) Related Tables: Buyer, Seller, Products Foreign Keys: product_id → products(id), buyer_id → users(id), seller_id → users(id) Columns (13 total): - id (INT, PRIMARY KEY, AUTO_INCREMENT) - product_id (INT, FK to products) - buyer_id (INT, FK to users) - seller_id (INT, FK to users) - offer_type (ENUM: buy, rent) - offer_price (DECIMAL 10,2) - message (TEXT) - rental_start_date (DATE) - rental_end_date (DATE) - deposit_amount (DECIMAL 10,2) - status (ENUM: pending, accepted, rejected, withdrawn) - seller_remarks (TEXT) - can_reject_until (DATETIME) ⭐ NEW - 1 day from offer time - buyer_reliability_at_offer (DECIMAL 3,2) ⭐ NEW - Snapshot at offer time - created_at (DATETIME) - updated_at (DATETIME) Dynamic Values: - Rental date conflict validation - Can_reject_until auto-calculated (1 day limit) - Buyer reliability snapshot captured ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ TABLE 6: orders ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ Purpose: Store confirmed orders from accepted offers Related Tables: Buyer, Seller, Products, Delivery Person Foreign Keys: buyer_id → users(id), seller_id → users(id), product_id → products(id), delivery_person_id → users(id) Columns (27 total): - id (INT, PRIMARY KEY, AUTO_INCREMENT) - order_number (VARCHAR 50, UNIQUE) - Format: FLX000001 - product_id (INT, FK to products) - buyer_id (INT, FK to users) - seller_id (INT, FK to users) - delivery_person_id (INT, FK to users, NULL) - order_type (ENUM: buy, rent) - final_price (DECIMAL 10,2) - deposit_amount (DECIMAL 10,2) - fitting_charge (DECIMAL 10,2) - rental_start_date (DATE) ⭐ NEW - For rent orders - rental_end_date (DATE) ⭐ NEW - For rent orders - original_rental_end_date (DATE) ⭐ NEW - Before extension - is_extended (TINYINT, DEFAULT 0) ⭐ NEW - Extension flag - extension_days (INT, DEFAULT 0) ⭐ NEW - Days extended - pickup_date (DATETIME) ⭐ NEW - Delivery pickup schedule - drop_date (DATETIME) ⭐ NEW - Delivery drop schedule - self_delivery (TINYINT, DEFAULT 0) ⭐ NEW - Self-delivery flag - delivery_type (ENUM: self, platform, co_delivery) ⭐ NEW - delivery_address (TEXT) - delivery_pin_code (VARCHAR 10) - status (ENUM: pending, confirmed, packed, shipped, delivered, cancelled, returned) - payment_status (ENUM: pending, paid, refunded) - delivery_date (DATETIME) - return_date (DATETIME) - created_at (DATETIME) - updated_at (DATETIME) Dynamic Values: - Order number auto-generated (FLX + padded ID) - Rental dates validated against calendar - Extension allowed 1 day before pickup - Pickup/drop dates auto-scheduled based on distance ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ TABLE 7: order_status_history ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ Purpose: Track status changes for orders (audit trail) Related Tables: Orders, Users Foreign Keys: order_id → orders(id), updated_by → users(id) Columns: - id (INT, PRIMARY KEY, AUTO_INCREMENT) - order_id (INT, FK to orders) - status (VARCHAR 50) - remarks (TEXT) - updated_by (INT, FK to users) - created_at (DATETIME) Dynamic Values: Auto-logged on every status change with user tracking ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ TABLE 8: transactions ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ Purpose: Store payment transactions for orders Related Tables: Orders, Users Foreign Keys: order_id → orders(id), user_id → users(id) Columns: - id (INT, PRIMARY KEY, AUTO_INCREMENT) - order_id (INT, FK to orders) - user_id (INT, FK to users) - transaction_type (ENUM: credit, debit) - amount (DECIMAL 10,2) - description (VARCHAR 255) - payment_method (VARCHAR 50) - transaction_id (VARCHAR 100) - External gateway reference - created_at (DATETIME) Dynamic Values: Auto-created on payment events, linked to payment gateway ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ TABLE 9: reviews ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ Purpose: Store buyer/seller reviews after order completion Related Tables: Orders, Users (reviewer and reviewed) Foreign Keys: order_id → orders(id), reviewer_id → users(id), reviewed_id → users(id) Columns (10 total): - id (INT, PRIMARY KEY, AUTO_INCREMENT) - order_id (INT, FK to orders) - reviewer_id (INT, FK to users) - reviewed_id (INT, FK to users) - reviewer_type (ENUM: buyer, seller) - rating (INT 1-5) - comment (TEXT) - can_review_until (DATETIME) ⭐ NEW - 20 days limit for self-review - is_self_review (TINYINT, DEFAULT 0) ⭐ NEW - 1 star only if self-review - created_at (DATETIME) Dynamic Values: - Can only review completed orders - Self-review limited to 1 star and 20 days window - Reliability scores auto-updated from reviews ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ TABLE 10: notifications ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ Purpose: Store user notifications Related Tables: All users (seller, buyer, delivery, admin) Foreign Keys: user_id → users(id) Columns (10 total): - id (INT, PRIMARY KEY, AUTO_INCREMENT) - user_id (INT, FK to users) - title (VARCHAR 255) - message (TEXT) - type (VARCHAR 50) - offer_received, order_status, payment, etc. - is_read (TINYINT, DEFAULT 0) - related_id (INT) - ID of related record (order, offer, etc.) - action_url (VARCHAR 255) ⭐ NEW - Link to related page - icon (VARCHAR 50) ⭐ NEW - Icon class/image - created_at (DATETIME) Dynamic Values: - Auto-created on system events - Real-time push notifications - Action URLs for deep linking ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ TABLE 11: delivery_persons ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ Purpose: Store delivery person additional details Related Tables: Users (delivery type) Foreign Keys: user_id → users(id) Columns (13 total): - id (INT, PRIMARY KEY, AUTO_INCREMENT) - user_id (INT, FK to users) - vehicle_type (VARCHAR 50) - vehicle_number (VARCHAR 50) - license_number (VARCHAR 50) - available_cities (TEXT) - is_available (TINYINT, DEFAULT 1) - total_deliveries (INT, DEFAULT 0) - rental_deliveries_count (INT, DEFAULT 0) ⭐ NEW - sale_deliveries_count (INT, DEFAULT 0) ⭐ NEW - rating (DECIMAL 3,2, DEFAULT 0.00) - created_at (DATETIME) - updated_at (DATETIME) Dynamic Values: - Availability status toggleable - Delivery counts auto-increment - Rating auto-updated from reviews - Badge eligibility (20+, 50+, 100+) ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ TABLE 12: admin_settings ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ Purpose: Store admin configuration settings Related Tables: Super Admin management Foreign Keys: None Columns: - id (INT, PRIMARY KEY, AUTO_INCREMENT) - setting_key (VARCHAR 100, UNIQUE) - setting_value (TEXT) - description (TEXT) - created_at (DATETIME) - updated_at (DATETIME) Dynamic Values (Key Settings): - max_sale_price_percentage (95% of original) - max_rent_deposit_percentage (10% of original) - max_rent_cost_percentage (40% of deposit) - platform_commission_percentage - gst_percentage - self_review_days_limit (20 days) - offer_rejection_days_limit (1 day) - rental_extension_days_before (1 day) - delivery_notification_hours_before (4-5 hours) - weekly_posting_limit - monthly_posting_limit - All configurable by super admin ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ TABLE 13: allowed_locations ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ Purpose: Store PIN codes/areas where service is available Related Tables: Admin management Foreign Keys: None Columns: - id (INT, PRIMARY KEY, AUTO_INCREMENT) - pin_code (VARCHAR 10) - city (VARCHAR 100) - state (VARCHAR 100) - is_active (TINYINT, DEFAULT 1) - created_at (DATETIME) Dynamic Values: - Admin can add/remove/deactivate locations - Validates user/product addresses against allowed areas ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ TABLE 14: subscription_packages ⭐ NEW ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ Purpose: Define buyer subscription packages (3/5/7/10 contacts) Related Tables: Buyers, User Subscriptions Foreign Keys: None Columns: - id (INT, PRIMARY KEY, AUTO_INCREMENT) - package_name (VARCHAR 100) - "3 Contacts", "5 Contacts", etc. - contact_limit (INT) - Number of seller contacts allowed - duration_type (ENUM: monthly, quarterly, yearly) - duration_months (INT) - 1, 3, or 12 - price (DECIMAL 10,2) - discount_percentage (DECIMAL 5,2) - is_active (TINYINT, DEFAULT 1) - created_at (DATETIME) - updated_at (DATETIME) Dynamic Values: - Admin can create/modify packages - Pricing configurable - Contact limits enforced in contact_views ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ TABLE 15: user_subscriptions ⭐ NEW ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ Purpose: Track active subscriptions for buyers Related Tables: Buyers, Subscription Packages Foreign Keys: user_id → users(id), package_id → subscription_packages(id) Columns: - id (INT, PRIMARY KEY, AUTO_INCREMENT) - user_id (INT, FK to users - buyer) - package_id (INT, FK to subscription_packages) - starts_at (DATETIME) - expires_at (DATETIME) - contacts_viewed (INT, DEFAULT 0) - Current count - is_active (TINYINT, DEFAULT 1) - payment_status (ENUM: pending, paid, failed) - created_at (DATETIME) - updated_at (DATETIME) Dynamic Values: - Contacts_viewed auto-increments on view - Expired subscriptions auto-deactivated - Prevents viewing if limit reached ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ TABLE 16: contact_views ⭐ NEW ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ Purpose: Track when buyers view seller contacts (enforce limits) Related Tables: Buyer, Seller, Products, User Subscriptions Foreign Keys: user_id → users(id), seller_id → users(id), product_id → products(id), subscription_id → user_subscriptions(id) Columns: - id (INT, PRIMARY KEY, AUTO_INCREMENT) - user_id (INT, FK to users - buyer) - seller_id (INT, FK to users - seller) - product_id (INT, FK to products) - subscription_id (INT, FK to user_subscriptions) - viewed_at (DATETIME) Unique Index: (user_id, seller_id) - Prevents duplicate seller views Dynamic Values: - One entry per unique buyer-seller pair - Enforces 3/5/7/10 contact limits - Blocked if subscription expired or limit reached ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ TABLE 17: bgv_records ⭐ NEW ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ Purpose: Background verification for delivery persons (KYC) Related Tables: Delivery Users, Admin Foreign Keys: user_id → users(id), verified_by → users(id) Columns: - id (INT, PRIMARY KEY, AUTO_INCREMENT) - user_id (INT, FK to users - delivery person) - pan_card (VARCHAR 20) - aadhar_card (VARCHAR 20) - pan_card_image (VARCHAR 255) - aadhar_card_image (VARCHAR 255) - bgv_status (ENUM: pending, verified, rejected) - verified_by (INT, FK to users - admin) - verified_at (DATETIME) - rejection_reason (TEXT) - remarks (TEXT) - created_at (DATETIME) - updated_at (DATETIME) Dynamic Values: - Mandatory KYC before seeing delivery requests - Admin approves/rejects - Delivery requests hidden until BGV verified ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ TABLE 18: categories ⭐ NEW ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ Purpose: Hierarchical taxonomy (Listing Type → Product Type → Category → Sub-Category) Related Tables: Products, Super Admin Foreign Keys: parent_id → categories(id) SELF, created_by → users(id) Columns: - id (INT, PRIMARY KEY, AUTO_INCREMENT) - parent_id (INT, FK to categories - self-referencing, NULL for root) - category_type (ENUM: listing_type, product_type, category, sub_category) - name (VARCHAR 100) - slug (VARCHAR 100) - description (TEXT) - display_order (INT, DEFAULT 0) - is_active (TINYINT, DEFAULT 1) - created_by (INT, FK to users - super admin) - created_at (DATETIME) - updated_at (DATETIME) Dynamic Values: - Super admin manages all levels - Cascading dropdowns on product upload - Tree structure: Listing Type → Product Type → Category → Sub-Category - Supports unlimited depth ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ TABLE 19: seller_payment_history ⭐ NEW ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ Purpose: Track seller payment events with flags Related Tables: Sellers, Orders Foreign Keys: seller_id → users(id), order_id → orders(id) Columns: - id (INT, PRIMARY KEY, AUTO_INCREMENT) - seller_id (INT, FK to users) - order_id (INT, FK to orders) - payment_type (ENUM: sale_payment, rent_payment, deposit_received, deposit_returned) - amount (DECIMAL 10,2) - status (ENUM: pending, sent, received) - flag (VARCHAR 50) - "Rent Sent", "Deposit Sent", "Payment Sent" - transaction_reference (VARCHAR 100) - created_at (DATETIME) Dynamic Values: - Auto-logged on payment events - Flags show in seller payment history - Tracks: Payment Sent, Rent Sent, Deposit Sent/Returned ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ TABLE 20: buyer_payment_history ⭐ NEW ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ Purpose: Track buyer payment events with flags Related Tables: Buyers, Orders Foreign Keys: buyer_id → users(id), order_id → orders(id) Columns: - id (INT, PRIMARY KEY, AUTO_INCREMENT) - buyer_id (INT, FK to users) - order_id (INT, FK to orders) - payment_type (ENUM: sale_payment, rent_payment, deposit_paid, deposit_refunded) - amount (DECIMAL 10,2) - status (ENUM: pending, deducted, refunded) - flag (VARCHAR 50) - "Rent Deducted", "Deposit Deducted", "Payment Deducted" - transaction_reference (VARCHAR 100) - created_at (DATETIME) Dynamic Values: - Auto-logged on payment events - Flags show in buyer transaction history - Tracks: Payment Deducted, Rent Deducted, Deposit Deducted/Refunded ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ TABLE 21: migrations (System Table) ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ Purpose: CodeIgniter 4 migration tracking Related Tables: System use only Foreign Keys: None Columns: - id (BIGINT, PRIMARY KEY, AUTO_INCREMENT) - version (VARCHAR 255) - class (VARCHAR 255) - group (VARCHAR 255) - namespace (VARCHAR 255) - time (INT) - batch (INT UNSIGNED) Dynamic Values: Auto-managed by CI4 migration system ================================================================================ FOREIGN KEY RELATIONSHIPS ================================================================================ Parent Table → Child Table | Relationship | FK Column -------------------------------------------------------------------------------- users → brands | 1:N | seller_id users → products | 1:N | seller_id users → offers (buyer) | 1:N | buyer_id users → offers (seller) | 1:N | seller_id users → orders (buyer) | 1:N | buyer_id users → orders (seller) | 1:N | seller_id users → orders (delivery) | 1:N | delivery_person_id users → order_status_history | 1:N | updated_by users → transactions | 1:N | user_id users → reviews (reviewer) | 1:N | reviewer_id users → reviews (reviewed) | 1:N | reviewed_id users → notifications | 1:N | user_id users → delivery_persons | 1:1 | user_id users → user_subscriptions | 1:N | user_id users → contact_views (buyer) | 1:N | user_id users → contact_views (seller) | 1:N | seller_id users → bgv_records (delivery) | 1:1 | user_id users → bgv_records (admin) | 1:N | verified_by users → categories | 1:N | created_by users → seller_payment_history | 1:N | seller_id users → buyer_payment_history | 1:N | buyer_id brands → products | 1:N | brand_id products → product_images | 1:N | product_id products → offers | 1:N | product_id products → orders | 1:N | product_id products → contact_views | 1:N | product_id offers → (becomes orders) | 1:1 | (when accepted) orders → order_status_history | 1:N | order_id orders → transactions | 1:N | order_id orders → reviews | 1:N | order_id orders → seller_payment_history | 1:N | order_id orders → buyer_payment_history | 1:N | order_id subscription_packages→ user_subscriptions | 1:N | package_id user_subscriptions → contact_views | 1:N | subscription_id categories → categories (self) | 1:N | parent_id Total Foreign Keys: 34 ================================================================================ BUSINESS LOGIC SUMMARY ================================================================================ 1. SUBSCRIPTION SYSTEM (Phase 1) - Buyers purchase packages: 3/5/7/10 seller contacts - contact_views tracks unique seller views - Prevents duplicate counting (UNIQUE index on buyer-seller pair) - Blocks viewing if limit reached or subscription expired 2. RELIABILITY SCORING (Phase 1) - Separate scores for: buyer_rent, buyer_buy, seller_rent, seller_sell - delivery_rental, delivery_sale scores - Auto-calculated from reviews after order completion - Displayed on profiles, used for filtering 3. RENTAL WORKFLOW (Phase 1) - Rental start/end dates in orders - Extension allowed 1 day before pickup (B17) - Original date preserved for tracking - Return dates tracked for rental completion 4. PRICING CALCULATIONS (Phase 1) - original_purchase_price required for sell/rent - suggested_sale_price = 95% of original (max, configurable) - suggested_deposit = 10% of original (max, configurable) - suggested_rent_price = 40% of deposit (max, configurable) - Seller can override but not exceed limits 5. BGV VERIFICATION (Phase 1) - Delivery persons must submit PAN + Aadhar - Admin/Super Admin verifies - Delivery requests hidden until BGV = verified - Status tracked in bgv_records table 6. SELF-DELIVERY OPTIONS (Phase 1) - Buyers choose: self-delivery, platform delivery, co-delivery - self_delivery flag in orders - No delivery person assigned if self_delivery = 1 - Affects pricing and commission calculations 7. OFFER REJECTION LIMITS (Phase 1) - Seller can reject offer within 1 day (configurable) - can_reject_until auto-calculated on offer creation - After 1 day, rejection not allowed if accepted offer exists 8. SELF-REVIEW RESTRICTIONS (Phase 1) - Buyer can review own product if not returned within 20 days - is_self_review = 1, rating limited to 1 star only - can_review_until calculated from order completion 9. PAYMENT TRACKING (Phase 2) - seller_payment_history: Payment Sent, Rent Sent, Deposit Sent/Returned - buyer_payment_history: Payment Deducted, Rent Deducted, Deposit Deducted/Refunded - Flags displayed in transaction history for clear tracking 10. HIERARCHICAL CATEGORIES (Phase 2) - categories table supports tree structure - Listing Type → Product Type → Category → Sub-Category - Super admin manages all levels - Cascading dropdowns on product upload form - Future-ready for unlimited depth ================================================================================ MIGRATION EXECUTION ORDER ================================================================================ Phase 1 (Original): 1. users 2. brands 3. products 4. product_images 5. offers 6. orders 7. order_status_history 8. transactions 9. reviews 10. notifications 11. delivery_persons 12. admin_settings 13. allowed_locations Phase 2 (New Tables): 14. subscription_packages 15. user_subscriptions 16. contact_views 17. bgv_records 18. categories 19. seller_payment_history 20. buyer_payment_history Phase 3 (ALTER Existing): 21. Add 7 reliability score columns to users 22. Add 5 pricing/category columns to products 23. Add 2 delivery count columns to delivery_persons 24. Add 9 rental/delivery columns to orders 25. Add 2 business logic columns to offers 26. Add 2 self-review columns to reviews 27. Add 2 rich notification columns to notifications Total Migrations: 27 files (13 original + 7 new tables + 7 alterations) ================================================================================ INDEXES & CONSTRAINTS ================================================================================ Unique Indexes: - users.email - orders.order_number - admin_settings.setting_key - contact_views (user_id, seller_id) - Prevents duplicate seller views Primary Keys: All tables have AUTO_INCREMENT id Foreign Key Constraints: 34 total - CASCADE on DELETE for data integrity - SET NULL on delivery_person_id (optional delivery) - CASCADE on UPDATE for consistency ================================================================================ ESTIMATED DATABASE SIZE ================================================================================ Assumptions: 10,000 users, 50,000 products, 100,000 orders Table | Est. Rows | Est. Size -------------------------|------------|---------- users | 10,000 | ~3 MB brands | 5,000 | ~1 MB products | 50,000 | ~20 MB product_images | 150,000 | ~10 MB offers | 200,000 | ~15 MB orders | 100,000 | ~30 MB order_status_history | 500,000 | ~20 MB transactions | 150,000 | ~10 MB reviews | 80,000 | ~8 MB notifications | 500,000 | ~25 MB delivery_persons | 1,000 | ~200 KB admin_settings | 50 | ~10 KB allowed_locations | 20,000 | ~1 MB subscription_packages | 20 | ~5 KB user_subscriptions | 5,000 | ~500 KB contact_views | 30,000 | ~2 MB bgv_records | 1,000 | ~300 KB categories | 500 | ~100 KB seller_payment_history | 150,000 | ~10 MB buyer_payment_history | 150,000 | ~10 MB TOTAL | ~2,081,570 | ~165 MB (excluding indexes) With Indexes: ~250 MB With Growth (2x): ~500 MB ================================================================================ CONFIGURATION SETTINGS ================================================================================ Admin Settings (Seeder Required): Key | Default Value | Description -------------------------------------|---------------|-------------------------------- max_sale_price_percentage | 95 | Max % of original purchase price for sale max_rent_deposit_percentage | 10 | Max % of original for deposit max_rent_cost_percentage | 40 | Max % of deposit for rent/day platform_commission_percentage | 5 | Commission on transactions gst_percentage | 18 | GST on commissions self_review_days_limit | 20 | Days to allow self-review offer_rejection_days_limit | 1 | Days seller can reject offer rental_extension_days_before | 1 | Days before pickup to allow extension delivery_notification_hours_before | 5 | Hours before delivery notification weekly_posting_limit | 50 | Max products per week per seller monthly_posting_limit | 200 | Max products per month per seller badge_threshold_bronze | 20 | Deliveries for Bronze badge badge_threshold_silver | 50 | Deliveries for Silver badge badge_threshold_gold | 100 | Deliveries for Gold badge ================================================================================ PHASE 1 vs PHASE 2 FEATURES ================================================================================ PHASE 1 (Development Active): ✅ User roles and authentication ✅ Product listing (sell/rent) ✅ Offer system ✅ Order management ✅ Basic reviews ✅ Notifications ✅ Delivery person basic ✅ Admin approval workflow ✅ Subscription system (3/5/7/10 contacts) ✅ Contact view limits ✅ BGV verification ✅ Reliability scoring (separate for rent/buy/sale) ✅ Rental dates and extensions ✅ Self-delivery options ✅ Offer rejection limits ✅ Self-review restrictions PHASE 2 (Database Ready, Development Pending): 🔄 Dynamic cascading categories 🔄 Payment history flags 🔄 Advanced payment tracking 🔄 Discount system 🔄 Badge system 🔄 Advertisement management 🔄 Commission calculations 🔄 Analytics dashboard 🔄 Reporting system ================================================================================ END OF DOCUMENT ================================================================================ Generated: January 10, 2026 Project: Flex Marketplace Database: flex_market Total Tables: 21 Total Columns: 250+ Total Foreign Keys: 34 Status: ✅ Ready for Migration Execution ================================================================================