logo
ExamplesEditorPricingDocs
37.1kSign in

Vehicle fleet management database schema

How a vehicle fleet is modelled: vehicles and drivers, licence entitlements with expiry, assignment history, downsampled telematics and fault codes, maintenance due by mileage or date, work orders and parts, fuel, inspections and incidents.

Fleet Management ER diagram

Vehicle fleet management database schema n1 n1 n1 n1 n1 n1 n1 n1 n1 n1 n1 n1 n1 n1 n1 n1 n1 n1 vehicles idBIGINT vinVARCHAR registration_plateVARCHAR makeVARCHAR modelVARCHAR model_yearSMALLINT vehicle_typeVARCHAR fuel_typeVARCHAR ownershipVARCHAR lease_ends_onDATE acquired_onDATE disposed_onDATE odometer_kmINTEGER home_baseVARCHAR statusVARCHAR drivers idBIGINT employee_codeVARCHAR full_nameVARCHAR emailVARCHAR phoneVARCHAR hired_onDATE terminated_onDATE statusVARCHAR driver_licences idBIGINT driver_idBIGINT licence_numberVARCHAR issuing_countryCHAR issuing_regionVARCHAR licence_classVARCHAR endorsementVARCHAR issued_onDATE expires_onDATE verified_atTIMESTAMPTZ statusVARCHAR vehicle_assignments idBIGINT vehicle_idBIGINT driver_idBIGINT assignment_typeVARCHAR started_atTIMESTAMPTZ ended_atTIMESTAMPTZ start_odometer_kmINTEGER end_odometer_kmINTEGER handover_notesTEXT telematics_samples idBIGINT vehicle_idBIGINT recorded_atTIMESTAMPTZ sample_reasonVARCHAR odometer_kmINTEGER engine_hoursNUMERIC fuel_level_pctSMALLINT speed_kphSMALLINT latitudeNUMERIC longitudeNUMERIC ignition_onBOOLEAN fault_events idBIGINT vehicle_idBIGINT codeVARCHAR descriptionVARCHAR severityVARCHAR is_activeBOOLEAN first_seen_atTIMESTAMPTZ last_seen_atTIMESTAMPTZ occurrence_countINTEGER cleared_atTIMESTAMPTZ work_order_idBIGINT maintenance_schedul… idBIGINT vehicle_idBIGINT service_typeVARCHAR interval_kmINTEGER interval_daysINTEGER last_service_kmINTEGER last_service_onDATE next_due_kmINTEGER next_due_onDATE is_activeBOOLEAN work_orders idBIGINT vehicle_idBIGINT maintenance_sched…BIGINT order_numberVARCHAR work_order_typeVARCHAR statusVARCHAR opened_atTIMESTAMPTZ completed_atTIMESTAMPTZ odometer_kmINTEGER vendor_nameVARCHAR labour_hoursNUMERIC labour_cost_minorBIGINT parts_cost_minorBIGINT currency_codeCHAR parts idBIGINT part_numberVARCHAR nameVARCHAR manufacturerVARCHAR unit_cost_minorBIGINT currency_codeCHAR quantity_on_handINTEGER reorder_pointINTEGER is_activeBOOLEAN work_order_parts idBIGINT work_order_idBIGINT part_idBIGINT quantityINTEGER unit_cost_minorBIGINT currency_codeCHAR fuel_purchases idBIGINT vehicle_idBIGINT driver_idBIGINT purchased_atTIMESTAMPTZ litresNUMERIC unit_price_minorBIGINT total_minorBIGINT currency_codeCHAR odometer_kmINTEGER card_last4CHAR merchantVARCHAR is_flaggedBOOLEAN inspections idBIGINT vehicle_idBIGINT driver_idBIGINT inspection_typeVARCHAR performed_atTIMESTAMPTZ odometer_kmINTEGER resultVARCHAR defectsJSONB work_order_idBIGINT incidents idBIGINT vehicle_idBIGINT driver_idBIGINT occurred_atTIMESTAMPTZ reported_atTIMESTAMPTZ incident_typeVARCHAR latitudeNUMERIC longitudeNUMERIC location_descript…VARCHAR at_faultBOOLEAN descriptionTEXT police_report_num…VARCHAR claim_numberVARCHAR estimated_cost_mi…BIGINT currency_codeCHAR statusVARCHAR

Related database schemas

SQL Server
Logistics

International freight forwarding database schema

A SQL Server schema for international freight: bookings and incoterms, shipments and container consolidation, multi-leg sea, air and road journeys, carrier rate contracts, customs declarations, milestone events, documents, charges and invoicing.

PostgreSQL
Logistics

Last-mile delivery & route planning database schema

Delivery operations modelled properly: depots, drivers and shifts, vehicle capacity constraints, orders with time windows, routes planned per day, stops in planned and actual sequence, geofenced arrivals, failed attempts, proof of delivery and re-sequencing history.

PostgreSQL
Logistics

Parcel tracking and courier network schema

A courier network schema built on an append-only scan log: shipments and the packages inside them, hubs and planned routes, scan events as the only truth, derived status, delivery attempts, exceptions, proof of delivery, and promised versus actual delivery.

PostgreSQL
Logistics

Returns & RMA (reverse logistics) database schema

How returns are actually modelled: RMA authorisations and eligibility windows, versioned policies, return reasons, inbound inspection and grading, dispositions from restock to scrap, refunds against exchanges and store credit, labels, restocking fees and return-abuse signals.