-- updated 040423 DDH

-- Map: mapdata->map
-- Trait_study: mapdata->qtlmapdata->qtltraitstudy->traitstudy
-- QTL: mapdata->qtlmapdata->qtl
-- Locus: mapdata->map->maplocus->locus
--        mapdata->mapdatalocus->locus
-- Breakpoint: mapdata->map->mapbreakpoint->breakpoint
-- Breakpoint_interval: mapdata->map->mapbreakpointinterval->breakpointinterval
-- 2_point: removed

create table mapdata (
	id integer auto_increment primary key,
	name varchar(255),
	-- Female_parent
	femaleparent_germplasmid integer,
	-- Male_parent
	maleparent_germplasmid integer
) ENGINE=InnoDB;
create index name_index on mapdata (name);
create index femaleparent_germplasmid_index on mapdata (femaleparent_germplasmid);
create index maleparent_germplasmid_index on mapdata (maleparent_germplasmid);

create table mapdataexternaldb (
        id integer auto_increment primary key,
	mapdataid integer,
	-- External_DB[0]
	name varchar(100),
	-- External_DB[1]
	accession varchar(100),
	url varchar(100),
	remark varchar(255)
) ENGINE=InnoDB;
create index mapdataid_index on mapdataexternaldb (mapdataid);

create table mapdataspecies (
	id integer auto_increment primary key,
	mapdataid integer,
	-- Species
	speciesid integer
) ENGINE=InnoDB;
create index mapdataid_index on mapdataspecies (mapdataid);
create index speciesid_index on mapdataspecies (speciesid);

create table mapdataparent (
	id integer auto_increment primary key,
	mapdataid integer,
	-- Parent
	germplasmid integer
) ENGINE=InnoDB;
create index mapdataid_index on mapdataparent (mapdataid);
create index germplasmid_index on mapdataparent (germplasmid);

create table mapdatalocation (
        id integer auto_increment primary key,
	mapdataid integer,
	-- Linkage_Data.Chromosome
	chromosome varchar(25),
	-- Linkage_Data.Chromosome_arm
	chromosomearm varchar(25)
) ENGINE=InnoDB;
create index mapdataid_index on mapdatalocation (mapdataid);

create table mapdatatype (
	id integer auto_increment primary key,
	mapdataid integer,
	-- Type
	type varchar(100)
) ENGINE=InnoDB;
create index mapdataid_index on mapdatatype (mapdataid);

create table mapdataremark (
	id integer auto_increment primary key,
	mapdataid integer,
	-- Map_units, Remarks
	type varchar(50),
	remark text
) ENGINE=InnoDB;
create index mapdataid_index on mapdataremark (mapdataid);

create table mapdatareference (
	id integer auto_increment primary key,
	mapdataid integer,
	-- Reference
	referenceid integer
) ENGINE=InnoDB;
create index mapdataid_index on mapdatareference (mapdataid);
create index referenceid_index on mapdatareference (referenceid);

create table mapdataurl (
        id integer auto_increment primary key,
	mapdataid integer,
	-- URL
	url varchar(255),
	description varchar(255)
) ENGINE=InnoDB;
create index mapdataid_index on mapdataurl (mapdataid);

create table mapdatacontact (
	id integer auto_increment primary key,
	mapdataid integer,
	-- Contact
	colleagueid integer
) ENGINE=InnoDB;
create index mapdataid_index on mapdatacontact (mapdataid);
create index colleagueid_index on mapdatacontact (colleagueid);

create table mapdatadatacurator (
        id integer auto_increment primary key,
	mapdataid integer,
	-- Data_curated
	colleagueid integer,
	date varchar(25)
) ENGINE=InnoDB;
create index mapdataid_index on mapdatadatacurator (mapdataid);
create index colleagueid_index on mapdatadatacurator (colleagueid);

create table mapdataimage (
        id integer auto_increment primary key,
	mapdataid integer,
	-- Image
	imageid integer
) ENGINE=InnoDB;
create index mapdataid_index on mapdataimage (mapdataid);
create index imageid_index on mapdataimage (imageid);

create table mapdatalocus (
        id integer auto_increment primary key,
        -- Locus.Data[0]
        mapdataid integer,
	-- Locus[0]
        locusid integer,
        -- Locus[1]
        scoringdata text,
        -- Locus.Data[1].Mapped_with_probe
	-- Locus.Data[1].Mapped_by_function
        howmapped varchar(100),
        howmapped_probeid integer,
        howmapped_geneid integer
) ENGINE=InnoDB;
create index mapdataid_index on mapdatalocus (mapdataid);
create index locusid_index on mapdatalocus (locusid);
create index howmapped_probeid_index on mapdatalocus (howmapped_probeid);
create index howmapped_geneid_index on mapdatalocus (howmapped_geneid);