car_mms/prisma/CAR_DATASET_README.md

Car Dataset System

Overview

The Car Dataset system provides a structured way to manage vehicle manufacturers, models, and body types. This ensures data consistency and improves user experience when adding new vehicles.

Database Schema

CarDataset Table

• id: Primary key (auto-increment)
• manufacturer: Vehicle manufacturer name (e.g., "Toyota", "Honda")
• model: Vehicle model name (e.g., "Camry", "Civic")
• bodyType: Vehicle body type (e.g., "Sedan", "SUV", "Hatchback")
• isActive: Boolean flag to enable/disable entries
• createdDate: Record creation timestamp
• updateDate: Record last update timestamp

Unique Constraint

• Combination of manufacturer and model must be unique
• This prevents duplicate entries for the same car model

How It Works

1. Vehicle Form Enhancement

The vehicle form now uses autocomplete inputs for:

• Manufacturer Selection: Users type to search from available manufacturers
• Model Selection: After selecting a manufacturer, users can search models for that manufacturer
• Body Type Auto-Fill: When a model is selected, the body type is automatically filled from the dataset

2. API Endpoints

• GET /api/car-dataset?action=manufacturers - Returns all unique manufacturers
• GET /api/car-dataset?action=models&manufacturer=Toyota - Returns models for a specific manufacturer
• GET /api/car-dataset?action=bodyType&manufacturer=Toyota&model=Camry - Returns body type for a specific model

3. User Experience Flow

1. User enters plate number
2. User starts typing manufacturer name → autocomplete shows matching manufacturers
3. User selects manufacturer → model field becomes enabled
4. User starts typing model name → autocomplete shows models for selected manufacturer
5. User selects model → body type is automatically filled
6. User continues with other vehicle details (year, transmission, etc.)

Seeding Data

Initial Dataset

The system comes pre-loaded with popular car manufacturers and models:

• Toyota (10 models)
• Honda (8 models)
• Nissan (9 models)
• Hyundai (8 models)
• Kia (8 models)
• Ford (8 models)
• Chevrolet (8 models)
• BMW (8 models)
• Mercedes-Benz (8 models)
• Audi (8 models)
• Lexus (8 models)
• Mazda (7 models)
• Mitsubishi (5 models)
• Subaru (6 models)
• Volkswagen (6 models)
• Infiniti (5 models)
• Acura (5 models)

Running the Seed

# Seed the car dataset
npx tsx prisma/carDatasetSeed.ts

Management Functions

Server Functions (app/lib/car-dataset-management.server.ts)

• getManufacturers() - Get all unique manufacturers
• getModelsByManufacturer(manufacturer) - Get models for a manufacturer
• getBodyType(manufacturer, model) - Get body type for a specific model
• createCarDataset(data) - Add new car dataset entry
• updateCarDataset(id, data) - Update existing entry
• deleteCarDataset(id) - Delete entry
• bulkImportCarDataset(data[]) - Bulk import multiple entries

Adding New Cars

To add new cars to the dataset, you can:

1. Use the bulk import function with an array of car data
2. Create individual entries using the create function
3. Manually insert into the database

Example:

await createCarDataset({
  manufacturer: "Tesla",
  model: "Model 3",
  bodyType: "Sedan"
});

Benefits

1. Data Consistency

• Standardized manufacturer and model names
• Consistent body type classifications
• Prevents typos and variations in naming

2. Improved User Experience

• Fast autocomplete search
• Reduced typing for common vehicles
• Automatic body type detection

3. Maintenance

• Easy to add new manufacturers and models
• Centralized vehicle data management
• Can disable outdated models without deleting data

4. Reporting

• Accurate statistics by manufacturer
• Consistent data for analytics
• Better search and filtering capabilities

Migration Steps

1. Database Migration

# Apply the schema changes
npx prisma db push

# Or run the SQL migration directly
sqlite3 prisma/dev.db < prisma/migrations/add_car_dataset.sql

2. Generate Prisma Client

npx prisma generate

3. Seed the Dataset

npx tsx prisma/carDatasetSeed.ts

4. Update Application

The vehicle form will automatically use the new car dataset system once the migration is complete.

Future Enhancements

Possible Additions

• Vehicle trim levels per model
• Engine specifications per model
• Year ranges for model availability
• Regional model variations
• Integration with external vehicle databases
• Admin interface for managing car dataset

API Extensions

• Search across all fields
• Pagination for large datasets
• Filtering by body type or year
• Export/import functionality
• Validation against external sources