vehicle type table structure

The Vehicle_Type table is a join table that pulls together many attributes of a vehicle from associated lookup tables.

Table Structure

Field	Type	NULL allowed	Default Value	Foreign key	Description
type_id*	INTEGER	NO			Unique identifier of this type
vehicle_class	INTEGER	YES		Vehicle_Class(vehicle_class)	The corresponding vehicle class (foreign key to the Vehicle_Class table)
connectivity_type	INTEGER	YES		Connectivity_Type(connectivity_type)	The corresponding connectivity type (foreign key to the Connectivity_Type table)
powertrain_type	INTEGER	YES		Powertrain_Type(powertrain_type)	The corresponding powertrain type (foreign key to the Powertrain_Type table) Values at powertrain_type.
automation_type	INTEGER	YES		Automation_Type(automation_type)	The corresponding automation type (foreign key to the Automation_Type table) Values at automation_type.
fuel_type	INTEGER	YES		Fuel_Type(fuel_type)	The corresponding fuel type (foreign key to the Fuel_Type table) Values at fuel_type.
vintage_type	INTEGER	YES		Vintage_Type(vintage_type)	The corresponding vintage (age) type (foreign key to the Vintage_Type table) Values at vintage_type.
ev_features_id	INTEGER	YES		EV_Features(ev_features_id)	The corresponding set of EV features (foreign key to the EV_Features table)
operating_cost_per_mile	REAL	NO	0.18		The cost to operate this vehicle for 1 mile (units: $USD/mi)

(* - Primary key)

The SQL statement for table and index creation is below.

CREATE TABLE "Vehicle_Type" (
  "type_id" INTEGER NOT NULL PRIMARY KEY,
  "vehicle_class" INTEGER NULL,
  "connectivity_type" INTEGER NULL,
  "powertrain_type" INTEGER NULL,
  "automation_type" INTEGER NULL,
  "fuel_type" INTEGER NULL,
  "vintage_type" INTEGER NULL,
  "ev_features_id" INTEGER NULL,
  "operating_cost_per_mile" REAL NOT NULL DEFAULT 0.18,

  CONSTRAINT "vehicle_class_fk"
    FOREIGN KEY ("vehicle_class")
    REFERENCES "Vehicle_Class" ("class_id")
    DEFERRABLE INITIALLY DEFERRED,
  CONSTRAINT "connectivity_type_fk"
    FOREIGN KEY ("connectivity_type")
    REFERENCES "Connectivity_Type" ("type_id")
    DEFERRABLE INITIALLY DEFERRED,
  CONSTRAINT "powertrain_type_fk"
    FOREIGN KEY ("powertrain_type")
    REFERENCES "Powertrain_Type" ("type_id")
    DEFERRABLE INITIALLY DEFERRED,
  CONSTRAINT "automation_type_fk"
    FOREIGN KEY ("automation_type")
    REFERENCES "Automation_Type" ("type_id")
    DEFERRABLE INITIALLY DEFERRED,
  CONSTRAINT "fuel_type_fk"
    FOREIGN KEY ("fuel_type")
    REFERENCES "Fuel_Type" ("type_id")
    DEFERRABLE INITIALLY DEFERRED,
  CONSTRAINT "vintage_type_fk"
    FOREIGN KEY ("vintage_type")
    REFERENCES "Vintage_Type" ("type_id")
    DEFERRABLE INITIALLY DEFERRED,
  CONSTRAINT "ev_features_id_fk"
    FOREIGN KEY ("ev_features_id")
    REFERENCES "EV_Features" ("ev_features_id")
    DEFERRABLE INITIALLY DEFERRED)

Enums

The following enums are used in this table.

powertrain_type

Enum Values

Value	Description
0	CONVENTIONAL
1	HEV
2	PHEV
3	BEV_SHORT
4	BEV_LONG
5	FCEV
6	CONVENTIONAL_48V
7	BEV_MICRO
8	MECHANICAL

automation_type

Enum Values

Value	Description
0	LEVEL0
1	LEVEL4
2	LEVEL5

fuel_type

Enum Values

Value	Description
0	GASOLINE
1	DIESEL
2	CNG
3	H2
4	ELECTRIC
5	HUMAN

vintage_type

Enum Values

Value	Description
0	VINTAGE_0_TO_5
1	VINTAGE_6_TO_10
2	VINTAGE_OVER_10