-- updated 040505 DDH

-- Trait_study: environment->traitstudyenvironment->traitstudy
-- Trait_scores: environment->traitscoreenvironment->traitscore
-- QTL: environment->qtlenvironment->qtl
-- Topography: removed
-- Drainage: removed
-- Testing_date: removed

create table environment (
	id integer auto_increment primary key,
	name varchar(255),
	-- Year
	year varchar(25),
	-- Location
	location varchar(255),
	-- Latitude
	latitude varchar(255),
	-- Longitude
	longitude varchar(255),
	-- Elevation
	elevation varchar(255),
	-- Experimental_design
	experimentaldesign varchar(255),
	-- Replications
	replications integer,
	-- Evaluator (see D.M. email 040503)
	evaluator_colleagueid integer,
	-- Institute
	institute varchar(255),
	-- Moisture
	moisture varchar(255),
	-- Planting_date
	plantingdate varchar(25),
	-- Harvest_date
	harvestdate varchar(25),
	-- N_applied (see D.M. email 040503)
	nitrogenapplied varchar(25),
	-- P2O5_applied (see D.M. email 040503)
	phosphorusapplied varchar(25),
	-- K2O_applied (see D.M. email 040503)
	potassiumapplied varchar(25)
) ENGINE=InnoDB;
create index name_index on environment (name);
create index evaluator_colleagueid_index on environment (evaluator_colleagueid);

create table environmentremark (
	id integer auto_increment primary key,
	environmentid integer,
	-- Soil_texture, Irrigation, Remarks
	type varchar(50),
	remark text
) ENGINE=InnoDB;
create index environmentid_index on environmentremark (environmentid);