A schema for uploading MOT test data, as suggested in the user guide with the exception of the `SET storage_engine=MyISAM` which is there to allow the `ENABLE KEYS\DISABLE KEYS` mechanism used in the large table uploads. Also the field `RFRINSPMANDESC TEXT(500) #Variation from suggested schema due to 1074 error` is suggested as `char` in the use…

MOT_setup_db.sql

SET storage_engine=MyISAM;
CREATE SCHEMA IF NOT EXISTS `MOT` ;
USE `MOT` ;

DROP TABLE IF EXISTS TESTRESULT;

CREATE TABLE TESTRESULT (
  TESTID INT UNSIGNED
	,VEHICLEID INT UNSIGNED
	,TESTDATE DATE
	,TESTCLASSID CHAR(2)
	,TESTTYPE CHAR(2)
	,TESTRESULT CHAR(3)
	,TESTMILEAGE INT UNSIGNED
	,POSTCODEREGION CHAR(2)
	,MAKE CHAR(30)
	,MODEL CHAR(30)
	,COLOUR CHAR(16)
	,FUELTYPE CHAR(1)
	,CYLCPCTY INT UNSIGNED
	,FIRSTUSEDATE DATE
	,PRIMARY KEY (TESTID)
	,INDEX IDX1 (TESTDATE, TESTTYPE, TESTRESULT, TESTCLASSID) 
	);

DROP TABLE IF EXISTS TESTITEM;

CREATE TABLE TESTITEM (
	TESTID INT UNSIGNED
	,RFRID SMALLINT UNSIGNED
	,RFRTYPE CHAR(1)
	,LATLOCATIONID CHAR(1)
	,LONGLOCATIONID CHAR(1)
	,VERTLOCATIONID CHAR(1)
	,DMARK CHAR(1)
	,INDEX IDX1 (TESTID)
	,INDEX IDX2 (RFRID)
	)
;

DROP TABLE IF EXISTS TESTITEM_DETAIL;

CREATE TABLE TESTITEM_DETAIL (
	RFRID SMALLINT UNSIGNED
	,TESTCLASSID CHAR(2)
	,TSTITMID SMALLINT UNSIGNED
	,MINORITEM CHAR(1)
	,RFRDESC CHAR(250)
	,RFRLOCMARKER CHAR(1)
	,RFRINSPMANDESC TEXT(500) #Variation from suggested schema due to 1074 error
	,RFRADVISORYTEXT CHAR(250)
	,TSTITMSETSECID SMALLINT UNSIGNED
	,PRIMARY KEY (RFRID, TESTCLASSID)
	,INDEX IDX1 (TSTITMID, TESTCLASSID)
	,INDEX IDX2 (TSTITMSETSECID, TESTCLASSID)
	)
;

DROP TABLE IF EXISTS TESTITEM_GROUP;

CREATE TABLE TESTITEM_GROUP (
	TSTITMID SMALLINT UNSIGNED
	,TESTCLASSID CHAR(2)
	,PARENTID SMALLINT UNSIGNED
	,TSTITMSETSECID SMALLINT UNSIGNED
	,ITEMNAME CHAR(100)
	,PRIMARY KEY (TSTITMID, TESTCLASSID)
	,INDEX IDX1 (PARENTID, TESTCLASSID)
	,INDEX IDX2(TSTITMSETSECID, TESTCLASSID)
	)
;

DROP TABLE IF EXISTS FAILURE_LOCATION;

CREATE TABLE FAILURE_LOCATION (
	FAILURELOCATIONID CHAR(1)
	,FAILURELOCATIONTXT CHAR(20)
	,PRIMARY KEY (FAILURELOCATIONID)
	)
;