Database Documentation
Vehicle Log Database — Schema & Design
Complete documentation of the vehicle_log database schema, including table structures, relationships, key design considerations, and sample queries for reporting.
Core Schema Elements
- Vehicle Tracking: Manage purchase details, model info, and automatic mileage synchronization.
- Fuel Logging: Record cost, volume, payment methods, and automated MPG calculations.
- Service History: Track services with recommended intervals and scheduled statuses.
- Access Control: Securely manage user roles and account assignments.
Key Design Considerations
Normalization: Strictly separated entities for users, vehicles, and logs to eliminate data redundancy.
Relational Integrity: InnoDB foreign keys enforce ON DELETE CASCADE and RESTRICT rules appropriately.
Precision: Used DECIMAL(10,2) for all monetary and geometric values to prevent floating-point errors.
Auditing: Automatic _modified timestamps for tracking database mutations.
Relational Architecture
Enhanced ERD visualization highlighting user assignments and log-to-vehicle relationships.
Table Structures
Users Table
Key columns: user_id (PK), first_name, last_name, email (U), user_password, user_role, is_active, date_created...
Vehicles Table
Key columns: vehicle_id (PK), vehicle_make, vehicle_model, vehicle_year, vehicle_VIN, assigned_user_id (FK), current_mileage...
Maintenance type Table
Key columns: maintenance_id (PK), maintenance_code, maintenance_type, recommended_interval_miles, recommended_interval_days...
Maintenance Table
Key columns: maintenance_id (PK), vehicle_id (FK), maintenance_type_id (FK), maintenance_cost, maintenance_date, maintenance_status...
Fuel Table
Key columns: fuel_id (PK), vehicle_id (FK), fuel_date, fuel_gallons, fuel_cost_per_gallon, fuel_mileage, fuel_notes...
Vendors Table
Key columns: vendor_id (PK), vendor_name, vendor_phone, vendor_email, vendor_address, is_active...
Advanced Reporting Queries
1. Predictive Maintenance Calculation
SELECT v.vehicle_make, mt.maintenance_type,
m.maintenance_mileage + mt.recommended_interval_miles AS next_due_mileage
FROM maintenance m
JOIN maintenance_type mt ON m.maintenance_type_id = mt.maintenance_id
JOIN vehicles v ON m.vehicle_id = v.vehicle_id;2. MPG Performance Metric
SELECT v.vehicle_model, f.fuel_date,
(f.fuel_mileage - LAG(f.fuel_mileage) OVER (PARTITION BY v.vehicle_id ORDER BY f.fuel_date))
/ f.fuel_gallons AS mpg
FROM fuel f JOIN vehicles v ON f.vehicle_id = v.vehicle_id;