DArTdb database model description

	KDDart core ER diagram Version: 2.7.0 Core module of KDDart. Implemented in MySQL with triggers.

[BACK TO TOP]

Region: Breeding methods	Breeding methods for specimens

Table: breedingmethod	Controlled vocabulary of breeding methodology

ColName	Datatype	PK	FK	AI	NN	Comments
BreedingMethodId	INTEGER	PK		AI	NN	internal id
BreedingMethodTypeId	INTEGER		FK		NN	type of breeding method related to class breedingmethod in generaltype table
BreedingMethodName	VARCHAR(100)				NN	breeding method name
BreedingMethodAcronym	VARCHAR(30)					short version of the breeding method name
BreedingMethodNote	TEXT					breeding method short description
BreedingMethodSymbol	VARCHAR(10)					symbol of breeding method (e.g. delimiter in pedigree string)

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`BreedingMethodId`
xbm_TypeId	INDEX	`BreedingMethodTypeId`
xbm_Name	UNIQUE	`BreedingMethodName`
xbm_Acronym	UNIQUE	`BreedingMethodAcronym`

Table: breedingmethodfactor	Additional virtual column values for Breeding Methods.

ColName	Datatype	PK	FK	NN	Comments
BreedingMethodId	INTEGER	PK	FK	NN	Breeding method id
FactorId	INTEGER	PK	FK	NN	Factor id
FactorValue	VARCHAR(254)			NN	Value of the virtual column for Breeding Method

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`BreedingMethodId`, `FactorId`
FactorId	INDEX	`FactorId`

[BACK TO TOP]

Region: Genotypes and Specimens

Table: genotype	List of genotypes available for trial units. Direct relation to the trial unit maybe sometimes problematic, especially in horticulture when one plant can be a single trial unit and can be a hybrid of a few genotypes. This is why specimen is established as a subunit of the genotype. In this case one genotype can have more than one specimen (plants, plant groups), which may grow in various locations. Synonym for genotype can be variety or gemplams and should be used as a generic category. Specimen permission fields are only inherited from this table, using trigger and DAL mechanism.

Table: genotype

List of genotypes available for trial units. Direct relation to the trial unit maybe sometimes problematic, especially in horticulture when one plant can be a single trial unit and can be a hybrid of a few genotypes. This is why specimen is established as a subunit of the genotype. In this case one genotype can have more than one specimen (plants, plant groups), which may grow in various locations. Synonym for genotype can be variety or gemplams and should be used as a generic category. Specimen permission fields are only inherited from this table, using trigger and DAL mechanism.

ColName	Datatype	PK	FK	AI	NN	DefaultValue	Comments
GenotypeId	INTEGER	PK		AI	NN		genotype id
TaxonomyId	INTEGER		FK				taxonomy id
GenotypeName	VARCHAR(255)				NN		genotype name
GenusId	INTEGER		FK		NN		genus / organism
SpeciesName	VARCHAR(255)						name in Latin - common naming conventions should be established - For use when using different species and a trial from another genus
GenotypeAcronym	VARCHAR(32)						short name of genotype
OriginId	INTEGER(10)				NN		Scource Identifier - possible Part of Plant Variety Rights Information - could refer to organisation or contact
CanPublishGenotype	TINYINT(1)				NN		flag if publicly available
GenotypeColor	VARCHAR(32)						Possibly to utilise as Part of Plant Variety Rights Information
GenotypeNote	VARCHAR(6000)						description
OwnGroupId	INTEGER				NN		group id which owns the record
AccessGroupId	INTEGER				NN	0	group id with access to the record (different than own group)
OwnGroupPerm	TINYINT				NN		permission for the own group members
AccessGroupPerm	TINYINT				NN	0	permission for the other group members
OtherPerm	TINYINT				NN	0	permission for all the other system users

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`GenotypeId`
xg_GenotypeNameGenusId	UNIQUE	`GenotypeName`, `GenusId`
xg_GenusId	INDEX	`GenusId`
xg_OriginId	INDEX	`OriginId`
xg_SpeciesName	INDEX	`SpeciesName`
xg_OwnGroupId	INDEX	`OwnGroupId`
xg_AccessGroupId	INDEX	`AccessGroupId`
xg_OwnGroupPerm	INDEX	`OwnGroupPerm`
xg_AccessGroupPerm	INDEX	`AccessGroupPerm`
xg_OtherPerm	INDEX	`OtherPerm`

Table: genotypealias	One genotype may have many historical names (aliases) under which it has been known.

ColName	Datatype	PK	FK	AI	NN	DefaultValue	Comments
GenotypeAliasId	INTEGER	PK		AI	NN		genotype alias id
GenotypeAliasName	VARCHAR(255)				NN		genotype alias name
GenotypeId	INTEGER		FK		NN		genotype id
GenotypeAliasType	INTEGER		FK				genotype alias type from generaltype table class genotypealias
GenotypeAliasStatus	INTEGER		FK				status of the alias (e.g. used, preferred, old, etc)
GenotypeAliasLang	VARCHAR(15)						language of the genotype alias name
IsGenotypeName	TINYINT				NN	0	flag if this name is a current genotype name - can point to only single record in all genotype records
GenusId	INTEGER		FK		NN		genus id - same as for main genotype record - to assure uniqness inside the genus - should be managed by trigger

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`GenotypeAliasId`
xga_GenotypeAliasName	INDEX	`GenotypeAliasName`
xga_GenotypeId	INDEX	`GenotypeId`
xga_GenotypeAliasType	INDEX	`GenotypeAliasType`
xga_GenotypeAliasStatus	INDEX	`GenotypeAliasStatus`
xga_GenotypeAliasLang	INDEX	`GenotypeAliasLang`
xga_GenotypeAliasUniq	UNIQUE	`GenusId`, `GenotypeAliasName`

Table: genotypefactor	Additional virtual columns for genotype descriptions.

ColName	Datatype	PK	FK	NN	Comments
GenotypeId	INTEGER	PK	FK	NN	genotype id
FactorId	INTEGER	PK	FK	NN	factor id
FactorValue	VARCHAR(255)			NN	value

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`GenotypeId`, `FactorId`
FactorId	INDEX	`FactorId`

Table: genus	Logical group of genotypes, but not necessary strictly botanically related. Also referred to as crop or organism.

ColName	Datatype	PK	FK	AI	NN	DefaultValue	Comments
GenusId	INTEGER	PK		AI	NN		genus id
GenusName	VARCHAR(32)				NN		genus name

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`GenusId`
xg_GenusName	UNIQUE	`GenusName`

Table: genotypespecimen	Maintains the many to many relationship between genotype and specimen. In situations such as with horticulture it may be necessary to define the specimen (individual plant) using many genotypes (hybrid or chimera plants, e.g. trees which have a different root stock and scion). Enables the definition of more common cases where multiple specimens from a genotype (e.g. individuals grown in different environments) need to be distinguished. Type can be added, but is not mandatory.

Table: genotypespecimen

Maintains the many to many relationship between genotype and specimen. In situations such as with horticulture it may be necessary to define the specimen (individual plant) using many genotypes (hybrid or chimera plants, e.g. trees which have a different root stock and scion). Enables the definition of more common cases where multiple specimens from a genotype (e.g. individuals grown in different environments) need to be distinguished. Type can be added, but is not mandatory.

ColName	Datatype	PK	FK	AI	NN	DefaultValue	Comments
GenotypeSpecimenId	INTEGER	PK		AI	NN		id of the group of genotype and plant combination
SpecimenId	INTEGER		FK		NN		id of the specimen
GenotypeId	INTEGER		FK		NN		id of the genotype
GenotypeSpecimenType	INTEGER		FK				relation to type - useful when a few genotypes compose specimen and one is of type scion and the other is rootstock
InheritanceFlag	TINYINT					1	flag to point to the genotype from which specimen would inherit permissions in case there is many genotypes for one specimen. Always on for genotypes having one or more specimens.

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`GenotypeSpecimenId`
xgp_genotype	INDEX	`GenotypeId`
xgp_specimen	INDEX	`SpecimenId`
xgp_gst	INDEX	`GenotypeSpecimenType`
xgp_InheritanceFlag	INDEX	`InheritanceFlag`

Table: specimen	Record for a specimen, i.e. individual plants or their representative groups. May appear to be artificial (i.e. if it is a representation of the whole genotype) however, provides consistency and allows the sampling of more plants (specimens) from the trial unit, as well as composing plant (specimen) from more than one genotype (e.g. in horticulture). Permissions to records are governed by (inherited from) genotype permissions. Relevant db trigger and DAL code takes care for this feature.

Table: specimen

Record for a specimen, i.e. individual plants or their representative groups. May appear to be artificial (i.e. if it is a representation of the whole genotype) however, provides consistency and allows the sampling of more plants (specimens) from the trial unit, as well as composing plant (specimen) from more than one genotype (e.g. in horticulture). Permissions to records are governed by (inherited from) genotype permissions. Relevant db trigger and DAL code takes care for this feature.

ColName	Datatype	PK	FK	AI	NN	DefaultValue	Comments
SpecimenId	INTEGER	PK		AI	NN		Individual specimen id
BreedingMethodId	INTEGER		FK		NN		id of the breeding method
SourceCrossingId	INTEGER		FK				id of the crossing - optional this specimen is a product of this particular cross
SpecimenName	VARCHAR(254)				NN		Specimen name
SpecimenBarcode	VARCHAR(64)						Optional specimen barcode - if assigned could be printed on label
IsActive	TINYINT(2)					1	Set to 0 if we want to indicate that it is no longer in production, program or some other binary switch
Pedigree	TEXT						Could be generated Purdy string from male and female parent ID (or some other than Purdy standard)
SelectionHistory	VARCHAR(254)						Can be siblings clones etc, where genotype name is the same. pulses use this a lot
FilialGeneration	INTEGER						Level of specimens being selfed, required when full selection history is not available
SpecimenNote	TEXT						Comments about specimen if applicable
OwnGroupId	INTEGER				NN		group id which owns the record
AccessGroupId	INTEGER				NN	0	group id which can access the recrod (different than own group)
OwnGroupPerm	TINYINT				NN		permission for group owning the record
AccessGroupPerm	TINYINT				NN	0	permission for access group
OtherPerm	TINYINT				NN	0	permission for all other system users

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`SpecimenId`
xs_SpecimenName	UNIQUE	`SpecimenName`
xs_SpecimenBarcode	UNIQUE	`SpecimenBarcode`
xs_BreedingMethodId	INDEX	`BreedingMethodId`
xs_IsActive	INDEX	`IsActive`
xs_OwnGroupId	INDEX	`OwnGroupId`
xs_AccessGroupId	INDEX	`AccessGroupId`
xs_OwnGroupPerm	INDEX	`OwnGroupPerm`
xs_AccessGroupPerm	INDEX	`AccessGroupPerm`
xs_OtherPerm	INDEX	`OtherPerm`
xs_SourceCrossingId	INDEX	`SourceCrossingId`

Table: specimenfactor	Additional virtual columns for specimens.

ColName	Datatype	PK	FK	NN	Comments
SpecimenId	INTEGER	PK	FK	NN	specimen id
FactorId	INTEGER	PK	FK	NN	factor id
FactorValue	VARCHAR(255)			NN	value

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`SpecimenId`, `FactorId`
FactorId	INDEX	`FactorId`

Table: pedigree	Table, which defines specimen (genotype) pedigrees (genealogy). This design assumes that one specimen may have more than 2 parents, which is never a case in reality, but in some scenarios (e.g. bulk pollination) this kind of information may need to be preserved.

ColName	Datatype	PK	FK	AI	NN	Comments
PedigreeId	INTEGER	PK		AI	NN	internal record id
SpecimenId	INTEGER		FK		NN	id of the specimen
ParentSpecimenId	INTEGER		FK		NN	id of another specimen, which is its parent
ParentType	INTEGER		FK		NN	Parent type (male female self) or others as in generaltype table in class parent
ParentTrialUnitSpecimenId	INTEGER		FK			id of the trial unit specimen
SelectionReason	VARCHAR(100)					Short description (optional) why the selection was made
NumberOfSpecimens	INTEGER					Number of Specimens: The number of a specific parent specimen used in a breeding process to make progeny specimen. e.g. FemaleSpecimenName: F; MaleSpecimenName: M; 1xF is crossed with 20xM; Pedigree holds: NumberOfSpecimens Female F is 1; NumberOfSpecimens Male M is 20

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`PedigreeId`
xpe_SpecimenId	INDEX	`SpecimenId`
xpe_ParentSpecimenId	INDEX	`ParentSpecimenId`
xpe_ParentType	INDEX	`ParentType`
xpe_SpPaType	UNIQUE	`SpecimenId`, `ParentSpecimenId`, `ParentType`
xpe_ParentTrialUnitSpecimenId	INDEX	`ParentTrialUnitSpecimenId`

Table: genotypealiasfactor	Additional info about genotype aliases

ColName	Datatype	PK	FK	NN	Comments
GenotypeAliasId	INTEGER	PK	FK	NN	genotype alias id
FactorId	INTEGER	PK	FK	NN	factor id
FactorValue	VARCHAR(255)			NN	factor value

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`GenotypeAliasId`, `FactorId`
FactorId	INDEX	`FactorId`

Table: genpedigree	This is the same as pedigree table for specimen, but allows to keep pedigree at genotype level if needed - e.g. some generalization is required, while specimen pedigree can be utilized for e.g. selection history only. Gives flexibility to users to select one or use both constructs to track parent/child relationships.

ColName	Datatype	PK	FK	AI	NN	Comments
GenPedigreeId	INTEGER	PK		AI	NN	GenPedigree id
GenotypeId	INTEGER		FK		NN	id of the genotype
ParentGenotypeId	INTEGER		FK		NN	id of the parent genotype
GenParentType	INTEGER		FK		NN	what is the type of parent (e.g. male, female, self, etc)
NumberOfGenotypes	INTEGER					optional number of parent genotypes perhaps useful to store

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`GenPedigreeId`
xgp_GenotypeId	INDEX	`GenotypeId`
xgp_ParentGenotypeId	INDEX	`ParentGenotypeId`
xgp_GenParentType	INDEX	`GenParentType`
xgp_GePaType	UNIQUE	`GenotypeId`, `ParentGenotypeId`, `GenParentType`

Table: specimenkeyword	keywords for a specimen

ColName	Datatype	PK	FK	AI	NN	Comments
SpecimenKeywordId	INTEGER	PK		AI	NN	specimen keyword id
KeywordId	INTEGER		FK		NN	keyword id for a specimen
SpecimenId	INTEGER		FK		NN	specimen id

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`SpecimenKeywordId`
sky_KeywordId	INDEX	`KeywordId`
sky_SpecimenId	INDEX	`SpecimenId`

Table: taxonomy	Taxonomy hierarchy. Store simple internal taxonomy information from species up and/or link to external taxonomy resources.

ColName	Datatype	PK	FK	AI	NN	Comments
TaxonomyId	INTEGER	PK		AI	NN	taxonomy id - internal id of this database
ParentTaxonomyId	INTEGER		FK			parent taxonomy id - what is directly above in hierarchy
TaxonomyName	INTEGER				NN	main name used
TaxonomyClass	VARCHAR(255)				NN	class - species, genera, kingdom
TaxonomySource	VARCHAR(255)					source of taxonomy data - e.g. NCBI, AviBase, World Flora Onlie
TaxonomyExtId	VARCHAR(254)					id in the external source - e.g. tax_id from NCBI
TaxonomyURL	VARCHAR(254)					api endpoint or permalink to fetch information
TaxonomyNote	TEXT					aliased name and extra information

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`TaxonomyId`
xtax_ParentTaxonomyId	INDEX	`ParentTaxonomyId`
xtax_TaxonomyName	INDEX	`TaxonomyName`
xtax_TaxonomyClass	INDEX	`TaxonomyClass`

[BACK TO TOP]

Region: Sample inventory	Inventory of samples, seeds, tissues, etc

Table: item	Items, such as seed bags, stored in the inventory. A generic term and may be used for as inventory for a variety of material.

ColName	Datatype	PK	FK	AI	NN	Comments
ItemId	INTEGER	PK		AI	NN	Id of the stored item
TrialUnitSpecimenId	INTEGER		FK			trial unit specimen id
UnitId	INTEGER		FK			Id of the unit if known for the item
ItemSourceId	INTEGER		FK			Id of the contact (who is the external source)
ContainerTypeId	INTEGER		FK			id of the container type
SpecimenId	INTEGER		FK		NN	id of the specimen (bit redundant with trial unit specimen, but since this it may not be present then at least specimen info is there)
ScaleId	INTEGER		FK			id of the device used to take measurement
StorageId	INTEGER		FK			id of the storage location
ItemTypeId	INTEGER		FK		NN	type of the item
ItemStateId	INTEGER		FK			id of the state description (e.g. damaged, thrown away, etc)
ItemBarcode	VARCHAR(32)					barcode on the item container
Amount	DECIMAL(16,3)					amount of the item in container
DateAdded	DATETIME				NN	date time when added
AddedByUserId	INTEGER		FK		NN	who added
LastMeasuredDate	DATETIME					date time when last updated
LastMeasuredUserId	INTEGER					who last updated
ItemOperation	SET('subsample','group')					in case item is derived from other items by taking sample or grouping (mixing) this can be defined here. Item parentage is defined in itemparent table
ItemNote	VARCHAR(254)					some comments
LastUpdateTimeStamp	DATETIME				NN	last update time

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`ItemId`
xi_ScaleId	INDEX	`ScaleId`
xi_StorageId	INDEX	`StorageId`
xi_ItemTypeId	INDEX	`ItemTypeId`
xi_ItemBarcode	UNIQUE	`ItemBarcode`
xi_AddedByUserId	INDEX	`AddedByUserId`
xi_LastMeasuredUserId	INDEX	`LastMeasuredUserId`
xi_DateAdded	INDEX	`DateAdded`
xi_LastMeasuredDate	INDEX	`LastMeasuredDate`
xi_ItemStateId	INDEX	`ItemStateId`
xi_ItemSourceId	INDEX	`ItemSourceId`
xi_ContainerTypeId	INDEX	`ContainerTypeId`
xi_SpecimenId	INDEX	`SpecimenId`
xi_UnitId	INDEX	`UnitId`
xi_TrialUnitSpecimenId	INDEX	`TrialUnitSpecimenId`

Table: itemfactor	Additional virtual columns for items.

ColName	Datatype	PK	FK	NN	Comments
ItemId	INTEGER	PK	FK	NN	item id
FactorId	INTEGER	PK	FK	NN	virtual column id
FactorValue	VARCHAR(254)			NN	value in column for an item id

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`ItemId`, `FactorId`
FactorId	INDEX	`FactorId`

Table: itemgroup	Arbitrary Grouping of inventory items which may assist future retrieval, e.g. to group bags to be later sown in a trial.

ColName	Datatype	PK	AI	NN	DefaultValue	Comments
ItemGroupId	INTEGER	PK	AI	NN		Item group id
ItemGroupName	VARCHAR(64)			NN		item group name
ItemGroupNote	VARCHAR(254)					comments about item group
AddedByUser	INTEGER					system user id, who created item group
DateAdded	DATETIME			NN		date time when item group added
Active	TINYINT(1)			NN	1	flag if group active

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`ItemGroupId`
xig_ItemGroupName	UNIQUE	`ItemGroupName`
xig_AddedByUser	INDEX	`AddedByUser`

Table: itemgroupentry	Maintains the many to many relationship between the item and itemgroup tables.

ColName	Datatype	PK	FK	AI	NN	DefaultValue	Comments
ItemId	INTEGER		FK		NN		item id
ItemGroupId	INTEGER		FK		NN		item group id

IndexName	IndexType	Columns
xige_ItemId	INDEX	`ItemId`
xige_ItemGroupId	INDEX	`ItemGroupId`

Table: itemparent	This is a bit complex, but allows full flexibility of splitting one item into many as well as grouping many items into one. Full trace of how the inventory items has been rearranged.

ColName	Datatype	PK	FK	AI	NN	Comments
ItemParentId	INTEGER	PK		AI	NN	internal id
ItemParentType	INTEGER		FK		NN	item parent type (different than parent type in generaltype table) class - itemparent
ItemId	INTEGER		FK		NN	newly created item id from other items
ParentId	INTEGER		FK		NN	item id of the parent item

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`ItemParentId`
xip_ItemId	INDEX	`ItemId`
xip_ParentId	INDEX	`ParentId`
xip_ItemParentType	INDEX	`ItemParentType`
xip_ItemIDParentID	UNIQUE	`ItemId`, `ParentId`

Table: itemlog	Logging actions, changes done to each inventory item

ColName	Datatype	PK	FK	AI	NN	Comments
ItemLogId	INTEGER	PK		AI	NN	item log id
LogTypeId	INTEGER		FK		NN	type, action to log
SystemUserId	INTEGER		FK		NN	who did/log that action
ItemId	INTEGER		FK		NN	item it was logged for
LogDateTime	DATETIME				NN	date time of action
LogMessage	VARCHAR(254)				NN	message or info logged

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`ItemLogId`
xil_LogTypeId	INDEX	`LogTypeId`
xil_SystemUserId	INDEX	`SystemUserId`
xil_ItemId	INDEX	`ItemId`
xil_LogDateTime	INDEX	`LogDateTime`

[BACK TO TOP]

Region: Sample values

Table: samplemeasurement	Measurement of the sample from trial unit for a particular trait/variate, by the operator on certain date/time. Measurement can be done for sample type if it does not refer to the entire trial unit.

ColName	Datatype	PK	FK	NN	DefaultValue	Comments
TrialUnitId	INTEGER	PK	FK	NN		trial unit id
TraitId	INTEGER	PK	FK	NN		id of the trait being measured
OperatorId	INTEGER	PK	FK	NN		user performing the measurement
InstanceNumber	TINYINT	PK		NN	1	next consecutive number of the measurement instance if all other values of primary key are the same
SampleTypeId	INTEGER	PK	FK	NN		sample type id
SMGroupId	INTEGER	PK	FK	NN	0	sample measurement group the measurement if part of - if any
TrialUnitSpecimenId	INTEGER	PK	FK	NN		optional value of trial unit specimen link if measurement is at the level of sub-trialunit
SurveyId	INTEGER		FK			optional value of survey id - if data point comes from a particular survey
MeasureDateTime	DATETIME			NN		date / time of the measurement
TraitValue	VARCHAR(255)			NN		measurement value
StateReason	VARCHAR(30)					optional value state e.g. reason for rejection

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`TrialUnitId`, `TraitId`, `OperatorId`, `InstanceNumber`, `SampleTypeId`, `SMGroupId`, `TrialUnitSpecimenId`
xsm_OperatorId	INDEX	`OperatorId`
xsm_TraitId	INDEX	`TraitId`
xsm_MeasureDateTime	INDEX	`MeasureDateTime`
xsm_SampleTypeId	INDEX	`SampleTypeId`
xsm_SMGroupId	INDEX	`SMGroupId`
xsm_TrialUnitSpecimenId	INDEX	`TrialUnitSpecimenId`
xsm_SurveyId	INDEX	`SurveyId`
xsm_TrialUnitId	INDEX	`TrialUnitId`

Table: smgroup	Sample measurements for some portions of the trials may be grouped to distinct them from the same measurements, which were done before, but need to be retained or to have two sets which can be compared. It can also be used as means to keep several versions of the same dataset

ColName	Datatype	PK	FK	AI	NN	Comments
SMGroupId	INTEGER	PK		AI	NN	internal group id
SMGroupName	VARCHAR(254)				NN	group name - has to be unique
TrialId	INTEGER		FK		NN	trial id measurements belong to - this is a constrain that grouping measurements between trials is not possible
OperatorId	INTEGER		FK		NN	user - owner of the group
SMGroupStatus	VARCHAR(20)					status of the group
SMGroupDateTime	DATETIME				NN	date and time of the group - possibly creation time or last update
SMGroupNote	TEXT					general comments for the group

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`SMGroupId`
smg_SMGroupName	UNIQUE	`SMGroupName`
smg_TrialId	INDEX	`TrialId`
smg_OperatorId	INDEX	`OperatorId`
smg_SMGroupDateTime	INDEX	`SMGroupDateTime`
smg_SMGroupStatus	INDEX	`SMGroupStatus`

Table: itemmeasurement	Measurement of the sample from item inventory for a particular trait/variate, by the operator on certain date/time. Measurement can be done for sample type if it does not refer to the entire inventory item.

ColName	Datatype	PK	FK	NN	DefaultValue	Comments
ItemId	INTEGER	PK	FK	NN		item id
TraitId	INTEGER	PK	FK	NN		id of the trait being measured
OperatorId	INTEGER	PK	FK	NN		user performing the measurement
InstanceNumber	INTEGER	PK		NN	1	next consecutive number of the measurement instance if all other values of primary key are the same
SampleTypeId	INTEGER	PK	FK	NN		sample type id
IMGroupId	INTEGER	PK	FK	NN	0	sample measurement group the measurement if part of - if any
MeasureDateTime	DATETIME					date / time of the measurement
TraitValue	VARCHAR(255)					measurement value
StateReason	VARCHAR(255)					optional value state e.g. reason for rejection

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`ItemId`, `TraitId`, `OperatorId`, `InstanceNumber`, `SampleTypeId`, `IMGroupId`
xim_TraitId	INDEX	`TraitId`
xim_OperatorId	INDEX	`OperatorId`
xim_SampleTypeId	INDEX	`SampleTypeId`
xim_IMGroupId	INDEX	`IMGroupId`
xim_MeasureDateTime	INDEX	`MeasureDateTime`

Table: imgroup	Item measurements for some portions of the item data may be grouped to distinct them from the same measurements, which were done before, but need to be retained or to have two sets which can be compared. It can also be used as means to keep several versions of the same dataset.

ColName	Datatype	PK	FK	AI	NN	Comments
IMGroupId	INTEGER	PK		AI	NN	internal group id
IMGroupName	VARCHAR(255)				NN	group name - has to be unique
OperatorId	INTEGER		FK		NN	user - owner of the group
IMGroupStatus	VARCHAR(20)					status of the group
IMGroupDateTime	DATETIME				NN	date and time of the group - possibly creation time or last update
IMGroupNote	TEXT					general comments for the group

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`IMGroupId`
ximg_IMGroupName	UNIQUE	`IMGroupName`
ximg_OperatorId	INDEX	`OperatorId`
ximg_IMGroupStatus	INDEX	`IMGroupStatus`
ximg_IMGroupDateTime	INDEX	`IMGroupDateTime`

Table: crossingmeasurement	Measurement of the sample from crossing for a particular trait/variate, by the operator on certain date/time. Measurement can be done for sample type if it does not refer to the entire crossing.

ColName	Datatype	PK	FK	AI	NN	Comments
CrossingId	INTEGER	PK	FK	AI	NN	crossing id
TraitId	INTEGER	PK	FK		NN	id of the trait being measured
OperatorId	INTEGER	PK	FK		NN	user performing the measurement
InstanceNumber	TINYINT	PK			NN	next consecutive number of the measurement instance if all other values of primary key are the same
SampleTypeId	INTEGER	PK	FK		NN	sample type id
CMGroupId	INTEGER	PK	FK		NN	crossing measurement group the measurement if part of - if any
MeasureDateTime	DATETIME					date / time of the measurement
TraitValue	VARCHAR(255)					measurement value
StateReason	VARCHAR(30)					optional value state e.g. reason for rejection

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`CrossingId`, `TraitId`, `OperatorId`, `InstanceNumber`, `SampleTypeId`, `CMGroupId`
xcm_TraitId	INDEX	`TraitId`
xcm_OperatorId	INDEX	`OperatorId`
xcm_SampleTypeId	INDEX	`SampleTypeId`
xcm_MeasureDateTime	INDEX	`MeasureDateTime`
xcm_CMGroupId	INDEX	`CMGroupId`

Table: cmgroup	Crossing measurements for some portions of the trials may be grouped to distinct them from the same measurements, which were done before, but need to be retained or to have two sets which can be compared. It can also be used as means to keep several versions of the same dataset.

ColName	Datatype	PK	FK	AI	NN	Comments
CMGroupId	INTEGER	PK		AI	NN	internal group id
CMGroupName	VARCHAR(255)				NN	group name - has to be unique
TrialId	INTEGER		FK		NN	trial id measurements belong to - this is a constrain that grouping measurements between trials is not possible
OperatorId	INTEGER		FK		NN	user - owner of the group
CMGroupStatus	VARCHAR(20)					status of the group
CMGroupDateTime	DATETIME				NN	date and time of the group - possibly creation time or last update
CMGroupNote	TEXT					general comments for the group

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`CMGroupId`
xcmg_CMGroupName	UNIQUE	`CMGroupName`
xcmg_TrialId	INDEX	`TrialId`
xcmg_OperatorId	INDEX	`OperatorId`
xcmg_CMGroupStatus	INDEX	`CMGroupStatus`
xcmg_CMGroupDateTime	INDEX	`CMGroupDateTime`

[BACK TO TOP]

Region: Site and project	Site and projects related group of tables. Spatial and logical groups of trials.

Table: site	Table storing sites, which may be whole farms, breeding stations or other general environmentally homogeneous areas, where planting occurs. Exact site geographic location is stored in siteloc table.

ColName	Datatype	PK	FK	AI	NN	Comments
SiteId	INTEGER	PK		AI	NN	site id
SiteTypeId	INTEGER		FK		NN	site type id
SiteName	VARCHAR(64)				NN	name
SiteAcronym	VARCHAR(5)				NN	short name of the site, can be used as e.g. part of the trial naming convention
CurrentSiteManagerId	INTEGER		FK		NN	person currently managing the site, not necessarily a user of this system, so linked to the contactId
SiteStartDate	DATETIME					Date when site started to exist
SiteEndDate	DATETIME					Date when site stopped to exist

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`SiteId`
xs_SiteName	INDEX	`SiteName`
xs_CurrentSiteManagerId	INDEX	`CurrentSiteManagerId`
xs_SiteAcronym	INDEX	`SiteAcronym`
xs_SiteStartDate	INDEX	`SiteStartDate`
xs_SiteEndDate	INDEX	`SiteEndDate`

Table: sitefactor	Additional virtual columns for site table.

ColName	Datatype	PK	FK	NN	Comments
SiteId	INTEGER	PK	FK	NN	site id
FactorId	INTEGER	PK	FK	NN	factor id
FactorValue	VARCHAR(255)			NN	value

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`SiteId`, `FactorId`
FactorId	INDEX	`FactorId`

Table: project	A very generic table to enable the grouping of trials according to some organisational distinction. Projects may have very different requirements therefore it maybe more appropriate to use the factor column to standardise data within the organisation.

ColName	Datatype	PK	FK	AI	NN	Comments
ProjectId	INTEGER	PK		AI	NN	project id
ProjectManagerId	INTEGER		FK		NN	manager of the project, link to contact table
TypeId	INTEGER		FK		NN	project type, link to general type table class project
ProjectName	VARCHAR(254)				NN	project name
ProjectStatus	VARCHAR(254)					project status (e.g. stage 2, confirmed, discontinued, etc)
ProjectStartDate	DATETIME					start project date
ProjectEndDate	DATETIME					end project date
ProjectNote	TEXT					project general description

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`ProjectId`
xp_ProjectManagerId	INDEX	`ProjectManagerId`
xp_TypeId	INDEX	`TypeId`
xp_ProjectName	UNIQUE	`ProjectName`
xp_ProjectStartDate	INDEX	`ProjectStartDate`
xp_ProjectEndDate	INDEX	`ProjectEndDate`
xp_ProjectStatus	INDEX	`ProjectStatus`

Table: projectfactor	Additional virtual columns for projects.

ColName	Datatype	PK	FK	NN	Comments
ProjectId	INTEGER	PK	FK	NN	project id
FactorId	INTEGER	PK	FK	NN	factor id
FactorValue	VARCHAR(254)			NN	value for project factor

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`ProjectId`, `FactorId`
FactorId	INDEX	`FactorId`

[BACK TO TOP]

Region: Specimen groups	Listing of specimen groups

Table: specimengroup	Defines a name for a group of specimens. Possible applications can be: many plants which went through selfing process; groups for next years testing; groups for genotyping.

ColName	Datatype	PK	FK	AI	NN	Comments
SpecimenGroupId	INTEGER	PK		AI	NN	id of the group of specimens
SpecimenGroupStatus	INTEGER		FK			current status of specimen group
SpecimenGroupTypeId	INTEGER		FK		NN	id of the specimen group type
SpecimenGroupName	VARCHAR(64)				NN	group name
SpecimenGroupNote	VARCHAR(254)					description
SpecimenGroupCreated	DATETIME				NN	date time when group was created
SpecimenGroupLastUpdate	DATETIME					date time when group was last updated
OwnGroupId	INTEGER					group id which owns the record
AccessGroupId	INTEGER					Group id allowed to access specimen group
OwnGroupPerm	TINYINT					permission for the own group members
AccessGroupPerm	TINYINT					permission for the access group members
OtherPerm	TINYINT					permission for all the other system users

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`SpecimenGroupId`
xsg_SpecimenGroupTypeId	INDEX	`SpecimenGroupTypeId`
xsg_SpecimenGroupName	UNIQUE	`SpecimenGroupName`
xsg_SpecimenGroupStatus	INDEX	`SpecimenGroupStatus`
xsg_SpecimenGroupCreated	INDEX	`SpecimenGroupCreated`
xsg_SpecimenGroupLastUpdate	INDEX	`SpecimenGroupLastUpdate`
xsg_AccessGroupId	INDEX	`AccessGroupId`
xsg_AccessGroupPerm	INDEX	`AccessGroupPerm`
xsg_OwnGroupId	INDEX	`OwnGroupId`
xsg_OwnGroupPerm	INDEX	`OwnGroupPerm`
xsg_OtherPerm	INDEX	`OtherPerm`

Table: specimengroupentry	Maintains the many to many relationship between specimens and specimens groups.

ColName	Datatype	PK	FK	AI	NN	Comments
SpecimenGroupEntryId	INTEGER	PK		AI	NN	entry id
SpecimenId	INTEGER	PK	FK		NN	specimen id
SpecimenGroupId	INTEGER	PK	FK		NN	specimen group id
SpecimenNote	VARCHAR(254)					special note for this specimen in the group

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`SpecimenGroupEntryId`, `SpecimenId`, `SpecimenGroupId`
xsge_SpecimenId	INDEX	`SpecimenId`
xsge_SpecimenGroupId	INDEX	`SpecimenGroupId`

[BACK TO TOP]

Region: Survey

Table: survey	Survey entity represents field data collection expedition. System users may arbitrary select points of interest (trial units) to visit in arbitrary order. This entity is for planning physical data collection events as well as for calculating an effort in such events.

ColName	Datatype	PK	FK	AI	NN	Comments
SurveyId	INTEGER	PK		AI	NN	Internal survey id
SurveyManagerId	INTEGER		FK		NN	Contact that is assigned as a survey manager
SurveyName	VARCHAR(255)				NN	Survey name
SurveyStartTime	DATETIME				NN	Survey start time
SurveyEndTime	DATETIME					Survey end time
SurveyNote	TEXT					Survey notes and comments
SurveyTypeId	INTEGER		FK			Type of Survey

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`SurveyId`
xs_SurveryName	UNIQUE	`SurveyName`
xs_SurveryStartTime	INDEX	`SurveyStartTime`
xs_SurveryEndTime	INDEX	`SurveyEndTime`
xs_SurveyManagerId	INDEX	`SurveyManagerId`

Table: surveytrait	Linking survey and traits - which traits will be scored during the survey. Can be a subset of traits for trials (experiments) involved, but can also be an arbitrary selection.

ColName	Datatype	PK	FK	AI	NN	DefaultValue	Comments
SurveyTraitId	INTEGER	PK		AI	NN		Internal survey trait id
SurveyId	INTEGER		FK		NN		Survey Id
TraitId	INTEGER		FK		NN		Trait Id
Compulsory	TINYINT				NN	0	Flag if scoring the trait is compulsory - by default it is not

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`SurveyTraitId`
xst_SurveryId	INDEX	`SurveyId`
xst_TraitId	INDEX	`TraitId`

Table: surveytrialunit	Linking survey with trial unit visited during the survey.

ColName	Datatype	PK	FK	AI	NN	Comments
SurveyTrialUnitId	INTEGER	PK		AI	NN	internal survey trial unit id
SurveyId	INTEGER		FK		NN	survey id
TrialUnitId	INTEGER		FK		NN	trial unit
VisitTime	DATETIME				NN	visit time
VisitOrder	INTEGER					consecutive order number during the visit
CollectorId	INTEGER		FK			id of the person who collected data

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`SurveyTrialUnitId`
xstu_SurveyId	INDEX	`SurveyId`
xstu_TrialUnitId	INDEX	`TrialUnitId`
xstu_VisitTime	INDEX	`VisitTime`
xstu_VisitOrder	INDEX	`VisitOrder`
xstu_CollectorId	INDEX	`CollectorId`

Table: surveyfactor	Additional virtual columns for genotype descriptions.

ColName	Datatype	PK	FK	NN	Comments
SurveyId	INTEGER	PK	FK	NN	Survey Id
FactorId	INTEGER	PK	FK	NN	Factor Id
FactorValue	VARCHAR(254)			NN	value

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`SurveyId`, `FactorId`
FactorId	INDEX	`FactorId`

[BACK TO TOP]

Region: System, users and contacts A	Users, contacts and organisations

Table: contact	List of general contacts (not only system users, but also collaborators, material providers, site managers, etc). Links with organisation.

ColName	Datatype	PK	FK	AI	NN	Comments
ContactId	INTEGER	PK		AI	NN	contact id
ContactLastName	VARCHAR(64)				NN	last name
ContactFirstName	VARCHAR(32)				NN	first name
ContactAcronym	VARCHAR(32)					acronym
ContactAddress	VARCHAR(128)					address
ContactTelephone	VARCHAR(14)					phone number
ContactMobile	VARCHAR(14)					mobile number
ContactEMail	VARCHAR(255)					e-mail
OrganisationId	INTEGER		FK		NN	organisation id

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`ContactId`
xc_LastFirstName	INDEX	`ContactLastName`, `ContactFirstName`
xc_OrganisationId	INDEX	`OrganisationId`
xc_ContactEMail	INDEX	`ContactEMail`

Table: contactfactor	Additional virtual columns for contacts.

ColName	Datatype	PK	FK	NN	Comments
ContactId	INTEGER	PK	FK	NN	contact id
FactorId	INTEGER	PK	FK	NN	factor id
FactorValue	VARCHAR(255)			NN	value

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`ContactId`, `FactorId`
FactorId	INDEX	`FactorId`

Table: organisation	List of collaborating and internal and/or external organisations.

ColName	Datatype	PK	FK	AI	NN	DefaultValue	Comments
OrganisationId	INTEGER	PK		AI	NN		organisation id
OrganisationName	VARCHAR(64)				NN		organisation name

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`OrganisationId`
xo_OrganisationName	UNIQUE	`OrganisationName`

Table: systemuser	List of the system users who are authorised to access the system.

ColName	Datatype	PK	FK	AI	NN	Comments
UserId	INTEGER	PK		AI	NN	user id
UserName	VARCHAR(32)				NN	user name
UserPassword	VARCHAR(128)				NN	user password
PasswordSalt	VARCHAR(64)				NN	password salt (used to hash/encrypt password?)
ContactId	INTEGER		FK		NN	contact id
LastLoginDateTime	DATETIME					date and time of last logon
UserPreference	TEXT					what preferences are stored here and in what format?
UserType	VARCHAR(20)				NN	distinguish between humans and mechanical devices for data input or processing
UserVerification	VARCHAR(255)					token for password reset
UserVerificationDT	DATETIME					password reset date and time

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`UserId`
xsu_UserName	UNIQUE	`UserName`
xsu_ContactId	INDEX	`ContactId`

Table: organisationfactor	Additional columns for organisations if needed

ColName	Datatype	PK	FK	NN	Comments
OrganisationId	INTEGER	PK	FK	NN	organisation id
FactorId	INTEGER	PK	FK	NN	factor id
FactorValue	VARCHAR(254)			NN	factor value

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`OrganisationId`, `FactorId`
FactorId	INDEX	`FactorId`

[BACK TO TOP]

Region: System, workflow, configuration and multimedia (B)	System - workflow definitions and multimedia, units and vocabularies, storage, etc

Table: activitylog	A log of system user activities

ColName	Datatype	PK	FK	AI	NN	Comments
ActivityLogId	INTEGER	PK		AI	NN	activity log id
UserId	INTEGER		FK		NN	user id
ActivityDateTime	DATETIME				NN	date time of the activity
ActivityLevel	INTEGER(10)				NN	Logout=2,Incorrect Password=3, Edit=101,Delete=102
ActivityText	VARCHAR(254)				NN	description of activity

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`ActivityLogId`
xal_UserId	INDEX	`UserId`

Table: authorisedsystemgroup	List of users in system group.

ColName	Datatype	PK	FK	AI	NN	Comments
AuthorisedSystemGroupId	INTEGER	PK		AI	NN	authorised system group id
UserId	INTEGER		FK		NN	user id
SystemGroupId	INTEGER		FK		NN	system group id
IsGroupOwner	TINYINT				NN	flag [0\|1] if the user group owner.

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`AuthorisedSystemGroupId`
xasg_UserId	INDEX	`UserId`
xasg_SystemGroupId	INDEX	`SystemGroupId`
xasg_IsGroupOwner	INDEX	`IsGroupOwner`

Table: systemgroup	Definitions of system groups. Most important in setting record level privileges for many entities.

ColName	Datatype	PK	AI	NN	Comments
SystemGroupId	INTEGER	PK	AI	NN	system group id
SystemGroupName	VARCHAR(64)			NN	system group name
SystemGroupDescription	VARCHAR(255)			NN	system group description

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`SystemGroupId`
xsg_SystemGroupName	UNIQUE	`SystemGroupName`

Table: deviceregister	Register of devices, which have access to the database to supply data. Devices to include those that supply environmental data or seed inventory data (e.g. scales) and extend to those that supply for phenotypic measurements. One device can record more than one data type / parameter to more than one environment layer.

ColName	Datatype	PK	FK	AI	NN	Comments
DeviceRegisterId	INTEGER	PK		AI	NN	Internal id
DeviceTypeId	INTEGER		FK		NN	device type id
DeviceId	VARCHAR(100)				NN	Unique device name / id under which it is registered in database
DeviceNote	VARCHAR(255)					Description of the device
Latitude	DECIMAL(16,14)					Latitude of the device in decimal degrees (-90, 90)
Longitude	DECIMAL(16,13)					Longitude of the device in decimal degrees (-180, 180)
DeviceConf	TEXT					Device configuration store - can be object e.g. json or yaml string etc

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`DeviceRegisterId`
xdr_DeviceId	UNIQUE	`DeviceId`
xdr_DeviceTypeId	INDEX	`DeviceTypeId`

Table: storage	General storage locations in a tree like hierarchy. Allows construction of locations such as: - Building 1 - Room 7 - Rack 5 - Shelf 299.

ColName	Datatype	PK	FK	AI	NN	Comments
StorageId	INTEGER	PK		AI	NN	id of the storage position
StorageBarcode	VARCHAR(64)					barcode of the storage position
StorageLocation	VARCHAR(32)				NN	location of the storage (e.g. building, room, freezer, shelf, etc)
StorageParentId	INTEGER		FK			id of the parent storage (e.g. for room parent storage could be building where the room is located)
StorageDetails	VARCHAR(254)					more info about a storage
StorageNote	VARCHAR(254)					detailed storage description

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`StorageId`
xs_StorageBarcode	UNIQUE	`StorageBarcode`
xs_StorageLocation	INDEX	`StorageLocation`
xs_StorageParentId	INDEX	`StorageParentId`

Table: generalunit	Defines the units used in various places in the system

ColName	Datatype	PK	FK	AI	NN	DefaultValue	Comments
UnitId	INTEGER	PK		AI	NN		unit id
UnitTypeId	INTEGER		FK				optional FK to type (e.g. weight, temperature, length etc) - class unittype
UnitName	VARCHAR(12)				NN		unit name (e.g. kg, dkg, etc)
UnitNote	VARCHAR(254)						some description
UnitSource	VARCHAR(254)						source infromation, convention, etc
UseByItem	TINYINT				NN	0	flag if want to use for item records
UseByTrait	TINYINT				NN	0	flag if want to use for trait records
UseByTrialEvent	TINYINT				NN	0	flag if want to use for trial event records
UseBylayerattrib	TINYINT				NN	0	flag if want to use in layer attributes in enviro module

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`UnitId`
xu_UnitName	UNIQUE	`UnitName`
xu_UnitTypeId	INDEX	`UnitTypeId`
xu_UseByItem	INDEX	`UseByItem`
xu_UseByTrait	INDEX	`UseByTrait`
xu_UseByTrialEvent	INDEX	`UseByTrialEvent`
xu_UseBylayerattrib	INDEX	`UseBylayerattrib`

Table: deviceregisterfactor	Additional virtual columns values for deivceregister.

ColName	Datatype	PK	FK	NN	Comments
DeviceRegisterId	INTEGER	PK	FK	NN	device register id
FactorId	INTEGER	PK	FK	NN	virtual column id
FactorValue	VARCHAR(254)			NN	value for column and device id

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`DeviceRegisterId`, `FactorId`
FactorId	INDEX	`FactorId`

Table: generaltype

Contains global vocabularies of types to support classification. The class column lists the possible classes of types. It also spans to types in Marker Module. With one exception of designtype table for trial all columns named *type refer to particular class of the types in this table. There are also some *status classifications contained here. Classes are: site, item, container, deviceregister, trial, operation, sample, specimengroup, specimengroupstatus, state, parent, itemparent, genotypespecimen, markerdataset, workflow, project, itemlog, plate, genmap, multimedia, tissue, genotypealias, genparent, genotypealiasstatus, traitgroup, unittype, trialgroup, breedingmethod, traitdatatype, season, trialunit, survey, extractdatatype

ColName	Datatype	PK	AI	NN	DefaultValue	Comments
TypeId	INTEGER	PK	AI	NN		general type id
Class	SET('site','item','container','deviceregister','trial','trialevent','sample','specimengroup','specimengroupstatus','state','parent','itemparent','genotypespecimen','markerdataset','workflow','project','itemlog','plate','genmap','multimedia','tissue','genotypealias','genparent','genotypealiasstatus','traitgroup','unittype','trialgroup','breedingmethod','traitdatatype','season','trialunit','survey','extractdatatype')			NN		class of type - possible values (site, item, container, deviceregister, trial, operation, sample, specimengroup, specimengroupstatus, state, parent, itemparent, genotypespecimen, markerdataset, workflow, project, itemlog, plate, genmap, multimedia, tissue, genotypealias, genparent, genotypealiasstatus, traitgroup, unittype, trialgroup, breedingmethod, traitdatatype, season, trialunit, survey, extractdatatype)
TypeName	VARCHAR(100)			NN		name of the type within notation
TypeNote	VARCHAR(254)					type description
IsTypeActive	TINYINT(1)			NN	1	0\|1 flag to indicate if type is active (can be used)
IsFixed	TINYINT(1)			NN	0	0\|1 flag to indicate if all values in the record should stay fixed
TypeMetaData	TEXT					metadata of type to better describe usage of type

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`TypeId`
xgt_ClassTypeName	UNIQUE	`Class`, `TypeName`
xgt_TypeName	INDEX	`TypeName`
xgt_IsTypeActive	INDEX	`IsTypeActive`

Table: generaltypefactor	Additional virtual columns for genotypes descriptions.

ColName	Datatype	PK	FK	NN	Comments
TypeId	INTEGER	PK	FK	NN	type id
FactorId	INTEGER	PK	FK	NN	virtual column id
FactorValue	VARCHAR(254)			NN	value of the virtual column

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`TypeId`, `FactorId`
FactorId	INDEX	`FactorId`

Table: barcodeconf	Table to store the configuration for barcodes used in the system. DAL should accept any barcode string in any table as long as it is unique. This table is an optional storage of information on how to generate barcode string automatically. Conventions for definitions are a subject for separate documentation.

ColName	Datatype	PK	AI	NN	Comments
BarcodeConfId	INTEGER	PK	AI	NN	configuration id
SystemTable	VARCHAR(32)			NN	configuration is for this table in the system
SystemField	VARCHAR(32)			NN	configuration is for this field (in the SystemTable)
BarcodeCode	VARCHAR(12)			NN	Name of barcode system (e.g. EAN13, Code39, QR, etc)
BarcodeDef	TEXT			NN	String with barcode definition

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`BarcodeConfId`

Table: workflow	Contains workflows used in the system for trial workflows and can also be used to link to other processes. Caters for the definition of multiple workflows for different situations.

ColName	Datatype	PK	FK	AI	NN	DefaultValue	Comments
WorkflowId	INTEGER	PK		AI	NN		workflow internal id
WorkflowName	VARCHAR(100)				NN		workflow name
WorkflowType	INTEGER		FK		NN		workflow type
WorkflowNote	VARCHAR(254)						some description about workflow
IsActive	INTEGER				NN	1	flag if it is active

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`WorkflowId`
wf_name	UNIQUE	`WorkflowName`
wf_type	INDEX	`WorkflowType`
wf_active	INDEX	`IsActive`

Table: workflowdef	List of steps for a workflow. StepOrder allows steps to be sorted as required.

ColName	Datatype	PK	FK	AI	NN	DefaultValue	Comments
WorkflowdefId	INTEGER	PK		AI	NN		workflow step id
WorkflowId	INTEGER		FK		NN		workflow id - this step is part of
StepName	VARCHAR(100)				NN		step name
StepOrder	TINYINT				NN	0	step order
StepNote	VARCHAR(254)						step description

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`WorkflowdefId`
wfd_workflow	INDEX	`WorkflowId`
wfd_name	INDEX	`StepName`
wfd_workflowname	UNIQUE	`WorkflowId`, `StepName`
wfd_workfloworder	UNIQUE	`WorkflowId`, `StepOrder`

Table: multimedia	This table stores references for multimedia files stored in KDDart system. Files will be attached to particular records in other tables, to e.g. illustrate processes, events or document.

ColName	Datatype	PK	FK	AI	NN	Comments
MultimediaId	INTEGER	PK		AI	NN	internal id
SystemTable	SET('genotype', 'specimen', 'project', 'site', 'trial', 'trialunit', 'item', 'extract', 'survey')				NN	name of the supported system table, for which file is attached
RecordId	INTEGER				NN	record id in the table
OperatorId	INTEGER		FK		NN	system user, who uploaded (updated) the file
FileType	INTEGER		FK		NN	file type (e.g. csv table, fasta sequence, image, video, etc)
OrigFileName	VARCHAR(254)				NN	name of the original file
HashFileName	VARCHAR(64)				NN	hash of the orignial file name
UploadTime	DATETIME				NN	time of upload, update of the file
FileExtension	VARCHAR(10)					file extension
MultimediaNote	TEXT					extended description of the multimedia

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`MultimediaId`
xmme_SystemTable	INDEX	`SystemTable`
xmme_RecordId	INDEX	`RecordId`
xmme_OperatorId	INDEX	`OperatorId`
xmme_FileType	INDEX	`FileType`

Table: conversionrule	This table defines conversion rules between units stored in the generalunit table. There can be only one rule per unique pair of from and to unit. Conversion formula should be a string, which evals to JavaScript code. JavaScript has engines and parsers on all platforms and in most modern languages, so it is the safest option to use this notation as close to be generic.

ColName	Datatype	PK	FK	AI	NN	DefaultValue	Comments
ConversionRuleId	INTEGER	PK		AI	NN		rule id
FromUnitId	INTEGER		FK		NN		id of the from unit
ToUnitId	INTEGER		FK		NN		id of the to unit
ConversionFormula	VARCHAR(7000)				NN	x * 1	formula, where x is the value of the from unit and result of the evaluation in a value in to unit. Formula should eval to JavaScript code.

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`ConversionRuleId`
xcr_FromUnitId	INDEX	`FromUnitId`
xcr_ToUnitId	INDEX	`ToUnitId`
xcr_UniqueUnits	UNIQUE	`FromUnitId`, `ToUnitId`

Table: keywordgroup	Arbitrary groups of keywords for easy search or other purpose.

ColName	Datatype	PK	FK	AI	NN	Comments
KeywordGroupId	INTEGER	PK		AI	NN	id of the keyword group
KeywordGroupName	VARCHAR(100)				NN	unique name of the keyword group
OperatorId	INTEGER		FK			optional operator id - if defined than group may belong to this particular user

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`KeywordGroupId`
kwg_KeywordGroupName	UNIQUE	`KeywordGroupName`, `OperatorId`
kwg_OperatorId	INDEX	`OperatorId`

Table: keyword	System wide collection of keywords

ColName	Datatype	PK	FK	AI	NN	Comments
KeywordId	INTEGER	PK		AI	NN	keyword id
KeywordName	VARCHAR(30)				NN	unique keyword
KeywordNote	VARCHAR(254)					keyword description
OperatorId	INTEGER		FK			optional owner of the keyword

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`KeywordId`
kw_KeywordNameOperator	UNIQUE	`KeywordName`, `OperatorId`
kw_OperatorId	INDEX	`OperatorId`

Table: keywordgroupentry	list of keywords in the keyword group(s)

ColName	Datatype	PK	FK	AI	NN	Comments
KeywordGroupEntryId	INTEGER	PK		AI	NN	keyword group entry id
KeywordId	INTEGER		FK		NN	keyword id in the group
KeywordGroupId	INTEGER		FK		NN	keyword group id

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`KeywordGroupEntryId`
kwge_KeywordId	INDEX	`KeywordId`
kwge_KeywordGroupId	INDEX	`KeywordGroupId`

Table: uniquenumber	Just to assure that every number provided in REST interface is always truly system wide unique.

ColName	Datatype	PK	FK	AI	NN	DefaultValue	Comments
UniqueNumberId	INTEGER	PK		AI	NN		sytem wide unique number

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`UniqueNumberId`

[BACK TO TOP]

Region: Trait and Treatment	Trait and Treatment related data

Table: genotypetrait	Table to store known characteristics for the genotype as a whole e.g. Average Flowing Dates, known disease resistance, observational traits like colour, etc. Useful if there have not been many (or any) trials on a particular genotype and at the same time there is some publicly known data.

ColName	Datatype	PK	FK	AI	NN	Comments
GenotypeTraitId	INTEGER(10)	PK		AI	NN	genotype trait id
GenotypeId	INTEGER		FK		NN	genotype id
TraitId	INTEGER		FK		NN	trait id
TraitValue	VARCHAR(255)				NN	known trait value, whatever the user specifies it to be, very generic

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`GenotypeTraitId`
xgt_GenotypeId	INDEX	`GenotypeId`
xgt_TraitId	INDEX	`TraitId`

Table: trait	Defines the measurement values for the genotype and trial. The specification of a validation rule is optional, however the format must adhere to either a regular or boolean expression. For example a validation rule could be: - Regular Expression TraitValRule=([A-Z]*) - Boolean Expression TraitValRule=(x\g1 and x\k50)

ColName	Datatype	PK	FK	AI	NN	DefaultValue	Comments
TraitId	INTEGER	PK		AI	NN		trait id
UnitId	INTEGER		FK		NN		id of the unit trait will be measured in
TraitGroupTypeId	INTEGER		FK				optional - class traitgroup - (e.g. all plant height related traits can be grouped in plantheight type
TraitName	VARCHAR(32)				NN		trait name
TraitCaption	VARCHAR(64)				NN		trait name (e.g. to display) shorter version or e.g. name without spaces
AltIdentifier	VARCHAR(254)						alternative identifier e.g. code used in another system like trait ontology or other
TraitDescription	VARCHAR(255)				NN		description about trait
TraitDataType	INTEGER		FK		NN		data type as per general type Class traitdatatype (e.g. DATE, TEXT, CATEGORICAL, ELAPSED_DAYS, INTEGER, DECIMAL) possibly others
TraitValueMaxLength	INTEGER(10)				NN		max length of the value (e.g. 12)
TraitLevel	SET('trialunit','subtrialunit','notetrialunit')				NN	trialunit	level at which trait is being used (scored), additional global distinction
IsTraitUsedForAnalysis	TINYINT(1)				NN		flag - can be used to streamline export, e.g export all that need analysis
TraitValRule	VARCHAR(255)				NN		validation rule for the value of the trait
TraitValRuleErrMsg	VARCHAR(255)				NN		error message to display, when validation rule criteria are not met
OwnGroupId	INTEGER				NN		group id owning the record
AccessGroupId	INTEGER				NN	0	group id with some access to the record
OwnGroupPerm	TINYINT				NN		owning group permissions
AccessGroupPerm	TINYINT				NN	0	other group permissions
OtherPerm	TINYINT				NN	0	all system users permissions

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`TraitId`
xt_TraitName	UNIQUE	`TraitName`
xt_TraitCaption	INDEX	`TraitCaption`
xt_TraitGroupTypeId	INDEX	`TraitGroupTypeId`
xt_OwnGroupId	INDEX	`OwnGroupId`
xt_AccessGroupId	INDEX	`AccessGroupId`
xt_OwnGroupPerm	INDEX	`OwnGroupPerm`
xt_AccessGroupPerm	INDEX	`AccessGroupPerm`
xt_OtherPerm	INDEX	`OtherPerm`
xt_TraitDataType	INDEX	`TraitDataType`
xt_TraitLevel	INDEX	`TraitLevel`
xt_AltIdentifier	UNIQUE	`AltIdentifier`

Table: traitalias	List of alias names for traits. Sometime for e.g. multinational organisations the same trait can be translated into several languages. The TraitLang column provides standard language code for translation interfaces.

ColName	Datatype	PK	FK	AI	NN	Comments
TraitAliasId	INTEGER	PK		AI	NN	trait alias id
TraitId	INTEGER		FK		NN	trait id
TraitAliasName	VARCHAR(64)				NN	name of trait alias
TraitAliasCaption	VARCHAR(64)					caption of the trait alias
TraitAliasDescription	VARCHAR(254)					description of the trait alias
TraitAliasValueRuleErrMsg	VARCHAR(254)					value rule error message of the trait alias
TraitLang	VARCHAR(6)					language code (e.g. en for English, sp for Spanish etc) in case trait alias is just a trait translation

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`TraitAliasId`
xta_TraitAliasName	INDEX	`TraitAliasName`
xta_TraitId	INDEX	`TraitId`
xta_TraitLang	INDEX	`TraitLang`

Table: treatment	Description (or value) for a treatment for trial unit (plot). Useful if trying to measure response to certain level of irrigations, fertilisation, etc.

ColName	Datatype	PK	FK	AI	NN	DefaultValue	Comments
TreatmentId	INTEGER	PK		AI	NN		treatment id
TreatmentText	VARCHAR(255)				NN		treatment description

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`TreatmentId`
xt_TreatmentText	UNIQUE	`TreatmentText`

Table: treatmentfactor	Additional virtual columns for treatment - where more detailed definitions are necessary.

ColName	Datatype	PK	FK	NN	Comments
TreatmentId	INTEGER	PK	FK	NN	treatment id
FactorId	INTEGER	PK	FK	NN	factor id
FactorValue	VARCHAR(32)			NN	value

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`TreatmentId`, `FactorId`
FactorId	INDEX	`FactorId`

Table: trialunittreatment	List of treatments for a trial unit.

ColName	Datatype	PK	FK	AI	NN	Comments
TrialUnitTreatmentId	INTEGER	PK		AI	NN	internal id
TreatmentId	INTEGER		FK		NN	treatment id - which treatment was used inside the unit
TrialUnitId	INTEGER		FK		NN	trial unit id - which trial unit is affected by a treatment
TreatmentDateTime	DATETIME					date time of the treatment
TrialUnitTreatmentNote	TEXT					note for treatment inside this trial unit

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`TrialUnitTreatmentId`
xttr_TreatmentId	INDEX	`TreatmentId`
xttr_TrialUnitId	INDEX	`TrialUnitId`

[BACK TO TOP]

Region: Trait groupings	Arbitrary groups of traits

Table: traitgroup	Group of traits. Can be used for various arbitrary purposes.

ColName	Datatype	PK	FK	AI	NN	Comments
TraitGroupId	INTEGER	PK		AI	NN	trait group id
TraitGroupName	VARCHAR(254)				NN	name of the group
AltIdentifier	VARCHAR(254)					alternative identified e.g. code used in another system like trait ontology
OperatorId	INTEGER		FK			optional operator id - if defined than group may belong to this particular user

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`TraitGroupId`
trg_TraitGroupName	UNIQUE	`TraitGroupName`, `OperatorId`
trg_OperatorId	INDEX	`OperatorId`
trg_AltIdentifier	UNIQUE	`AltIdentifier`

Table: traitgroupentry	List of trait group members

ColName	Datatype	PK	FK	AI	NN	Comments
TraitGroupEntryId	INTEGER	PK		AI	NN	trait group entry internal id
TraitId	INTEGER		FK		NN	trait id belonging to the group
TraitGroupId	INTEGER		FK		NN	trait group id

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`TraitGroupEntryId`
tge_TraitId	INDEX	`TraitId`
tge_TraitGroupId	INDEX	`TraitGroupId`

[BACK TO TOP]

Region: Trial events and workflow	Various operations and events happening on the entire trial. Design of the workflow.

Table: trialevent	Trial events are additional descriptors for operations (events) affecting an entire trial. Example events: - spray nitrogen 10 tons, - rain shed over trial during flowering season, - catastrophic rainfall or thunder, etc.

ColName	Datatype	PK	FK	AI	NN	Comments
TrialEventId	INTEGER	PK		AI	NN	trial event id
UnitId	INTEGER		FK		NN
EventTypeId	INTEGER		FK		NN	trial event type id
TrialId	INTEGER		FK		NN	trial id
OperatorId	INTEGER		FK		NN	person who performed operation
TrialEventValue	VARCHAR(32)				NN	event value (number in the units defined)
TrialEventDate	DATETIME				NN	operation date
TrialEventNote	VARCHAR(254)					additional description of the event

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`TrialEventId`
xte_OperatorId	INDEX	`OperatorId`
xte_TrialId	INDEX	`TrialId`
xte_EventTypeId	INDEX	`EventTypeId`
xte_TrialEventDate	INDEX	`TrialEventDate`

Table: trialeventfactor	Additional virtual columns descriptors for trial events. Some events (treatments) done to the trial. Can be a record of history, what was happening on the trial.

ColName	Datatype	PK	FK	NN	Comments
FactorId	INTEGER	PK	FK	NN	factor id
TrialEventId	INTEGER	PK	FK	NN	trial event id
FactorValue	VARCHAR(255)			NN	value

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`FactorId`, `TrialEventId`
FactorId	INDEX	`FactorId`

Table: trialworkflow	Some trials follow specific workflows and even if not everything is reflected in the database, certain steps are required to be done and recorded. This table could be such a record of each step or current status of the trial.

ColName	Datatype	PK	FK	AI	NN	DefaultValue	Comments
TrialWorkflowId	INTEGER	PK		AI	NN		internal id of trial workflow step
WorkflowdefId	INTEGER		FK		NN		id of workflow step
TrialId	INTEGER		FK		NN		id of the trial the workflow is attached to
CompleteBy	DATETIME						optional deadline to complete step
Completed	TINYINT				NN	0	flag if completed - default 0
ReminderAt	DATETIME						optional date and time of reminder
ReminderTo	VARCHAR(254)						optional e-mail list where to send reminders to
Note	TEXT						notes about this step

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`TrialWorkflowId`
twf_workflowdef	INDEX	`WorkflowdefId`
twf_trial	INDEX	`TrialId`

[BACK TO TOP]

Region: Trial groupings	Groupings of the trials for multilocation (multienvironmental) and possibly other purposes.

Table: trialgroup	Multi location trial grouping. Arbitrary grouping of trials, can be used e.g. for multienvironmental trials or group of trials in multi year experiment, which may or may not share the design.

ColName	Datatype	PK	FK	AI	NN	Comments
TrialGroupId	INTEGER	PK		AI	NN	Internal trial group id
TrialGroupOwner	INTEGER		FK		NN	system user id who ownes the trial group
TrialGroupType	INTEGER		FK		NN	trial group type from general type class trialgroup
TrialGroupName	VARCHAR(254)				NN	trial group unique name
TrialGroupStart	DATE					trial group or experiment start date
TrialGroupEnd	DATE					trial group or experiment end date
TrialGroupNote	TEXT					Long description of trial group
TrialGroupLayout	TEXT					layout of the trials in the group, e.g. relative positions to each other

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`TrialGroupId`
xtg_Name	UNIQUE	`TrialGroupName`
xtg_start	INDEX	`TrialGroupStart`
xtg_end	INDEX	`TrialGroupEnd`
xtg_owner	INDEX	`TrialGroupOwner`
xtg_type	INDEX	`TrialGroupType`

Table: trialgroupentry	Many to many link between trials and trial group

ColName	Datatype	PK	FK	AI	NN	Comments
TrialGroupEntryId	INTEGER	PK		AI	NN	internal trial group entry id
TrialGroupId	INTEGER		FK		NN	trial group id
TrialId	INTEGER		FK		NN	trial id - member of the trial group

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`TrialGroupEntryId`
xtge_trialid	INDEX	`TrialId`
xtge_trialgroupid	INDEX	`TrialGroupId`

Table: trialgroupfactor	Factor columns for trial groups

ColName	Datatype	PK	FK	NN	Comments
FactorId	INTEGER	PK	FK	NN	factor id
TrialGroupId	INTEGER	PK	FK	NN	trial group id
FactorValue	VARCHAR(254)			NN	value

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`FactorId`, `TrialGroupId`
FactorId	INDEX	`FactorId`

[BACK TO TOP]

Region: Trials and Units	Trial and Trial Units related data

Table: designtype	Each trial (experiment) has a design (layout/method) describing how the genotypes have been planted, the number of replicates, etc. Design type identifies the type of a trial design. For example: - Randomised Block using DiGGer - The trial design of this design type is generated by the DiGGer. - Unknown Design Type.

ColName	Datatype	PK	AI	NN	Comments
DesignTypeId	INTEGER	PK	AI	NN	design type id
DesignTypeName	VARCHAR(32)			NN	design type name
DesignSoftware	VARCHAR(255)				The executable file of the Software (such as DiGGer) that is used to design the trial of this design type.
DesignTemplateFile	TEXT				The template file defines how the parameter are required to be inserted in the input file for the design software.
DesignGenotypeFormat	VARCHAR(32)				Format in which the Specimen Name and Specimen Id will be exported into the trial design input file.
DesignFactorAliasPrefix	VARCHAR(16)				Prefix that will be used to find the factor for the Trial Design Parameter while importing trial design from the output file generated by the trial design software (such as DiGGer).

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`DesignTypeId`
xdt_DesignTypeName	UNIQUE	`DesignTypeName`

Table: trial	This table contains list of trials (equivalent to field experiments) performed within sites.

ColName	Datatype	PK	FK	AI	NN	DefaultValue	Comments
TrialId	INTEGER	PK		AI	NN		trial id
SeasonId	INTEGER		FK		NN		id of the season type from general type - to identify global concept of seasons
ProjectId	INTEGER		FK				id of the project that trial belongs to, it is optional
CurrentWorkflowId	INTEGER		FK				current workflow id to identify which workflow is currently assigned to trial. Optional as trial may not have a workflow assigned at all.
TrialTypeId	INTEGER		FK		NN		trial type id (general type, different from design type, which is trial specific definition)
SiteId	INTEGER		FK		NN		site id, to which trial belongs to
TrialName	VARCHAR(100)				NN		Trial name (can be created as concatenation of site, type, date, number)
TrialNumber	INTEGER				NN		trial running number
TrialAcronym	VARCHAR(30)				NN		alternative short name for a trial
DesignTypeId	INTEGER		FK		NN		design type - relation to design type table
TrialManagerId	INTEGER		FK		NN		person managing trial
TrialStartDate	DATETIME				NN		when started
TrialEndDate	DATETIME						when finished
TrialNote	VARCHAR(6000)						description text
TrialLayout	TEXT						object describing trial units layout - e.g. walking path, relative location of raw1, col1, etc
TULastUpdateTimeStamp	DATETIME				NN		date and time of trial unit last update - used for bulk update of trial units
OwnGroupId	INTEGER				NN		id of the group which owns the record
AccessGroupId	INTEGER				NN	0	id of the group which have permissions to the record (different to the own group)
OwnGroupPerm	TINYINT				NN		permissions of the own group to the record
AccessGroupPerm	TINYINT				NN	0	permissions of the other group to the record
OtherPerm	TINYINT				NN	0	permissions for all registered users to the record

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`TrialId`
xtr_TrialName	INDEX	`TrialName`
xtr_TrialAcronym	INDEX	`TrialAcronym`
xtr_ContactId	INDEX	`TrialManagerId`
xtr_DesignTypeId	INDEX	`DesignTypeId`
xtr_SiteId	INDEX	`SiteId`
xtr_TrialTypeId	INDEX	`TrialTypeId`
xtr_TrialStartDate	INDEX	`TrialStartDate`
xtr_TrialEndDate	INDEX	`TrialEndDate`
xtr_CurrentWorkflowId	INDEX	`CurrentWorkflowId`
xtr_ProjectId	INDEX	`ProjectId`
xtr_OwnGroupId	INDEX	`OwnGroupId`
xtr_AccessGroupId	INDEX	`AccessGroupId`
xtr_OwnGroupPerm	INDEX	`OwnGroupPerm`
xtr_AccessGroupPerm	INDEX	`AccessGroupPerm`
xtr_OtherPerm	INDEX	`OtherPerm`
xtr_SeasonId	INDEX	`SeasonId`

Table: trialfactor	Additional virtual columns for trial.

ColName	Datatype	PK	FK	NN	Comments
TrialId	INTEGER	PK	FK	NN	trial id
FactorId	INTEGER	PK	FK	NN	factor id
FactorValue	VARCHAR(255)			NN	value

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`TrialId`, `FactorId`
FactorId	INDEX	`FactorId`

Table: trialunit	This table stores information about each unit of the trial. It assumes that the unit is homogeneous. One trial unit can contain more than one specimen (e.g. for hybrid plants or when one plant died and another was replanted in place). Trial unit is a measured unit, which can be further divided by sample types distinguished within.

ColName	Datatype	PK	FK	AI	NN	DefaultValue	Comments
TrialUnitId	INTEGER	PK		AI	NN		Trial Unit Id
TrialId	INTEGER		FK		NN		trial id
SourceTrialUnitId	INTEGER		FK			0	Source Trial Unit that identifies the source of the sample used in the Trial Unit of the Trial. For example, the source trial unit for a wheat grain sample used in a milling trial can be a trial unit of a Wheat Variety Evaluation Trial. While importing data, the source file must provide information of one of the Alternate Key of Trial and the Unit Position of the source trial to identify the source trial unit.
ReplicateNumber	INTEGER(10)				NN		replicate number - next instance of the same specimen
TrialUnitBarcode	VARCHAR(254)						barcode of the trial unit (plot)
TrialUnitPosition	VARCHAR(254)						string describing the unit position e.g. can be concatenated list of dimensions
TrialUnitEntryId	INTEGER						the same as unit position, but just a numeric value - useful if wanting to order units in numeric fashion
TrialUnitX	INTEGER						X dimension of the trial unit (can be e.g. row)
TrialUnitY	INTEGER						Y dimension of the trial unit (can be e.g. column)
TrialUnitZ	INTEGER						Z dimension of the trial unit (can be e.g. block)
TrialUnitNote	VARCHAR(254)						additional description for the trial unit
SampleSupplierId	INTEGER(10)						sample supplier id (contact or organisation, no defined relation here). Optional field to define who supplied the the seed. Choosing if this is contact or organisation is a matter for organisations convention.
TrialUnitTypeId	INTEGER		FK				type of trial unit

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`TrialUnitId`
xtu_TrialUnitPosition	INDEX	`TrialUnitPosition`
xtu_SampleSupplierId	INDEX	`SampleSupplierId`
xtu_TrialUnitBarcode	UNIQUE	`TrialUnitBarcode`
xtu_TrialId	INDEX	`TrialId`
xtu_TrialUnitEntryId	INDEX	`TrialUnitEntryId`
xtu_TrialUnitX	INDEX	`TrialUnitX`
xtu_TrialUnitY	INDEX	`TrialUnitY`
xtu_TrialUnitZ	INDEX	`TrialUnitZ`
xtu_SourceTrialUnitId	INDEX	`SourceTrialUnitId`

Table: trialunitspecimen

Maintains the many to many relationship between trialunit and specimens. If specimen is the representation of the whole genotype (or composition of genotypes), it will have a single record here. Allows the sampling of multiple specimens from a unit or even same specimen multiple times in the unit distinguished by specimen number. For instances of long lived trials (e.g. tree living 30 years) this enables the reassignment of the same plant into different trial units.

ColName	Datatype	PK	FK	AI	NN	DefaultValue	Comments
TrialUnitSpecimenId	INTEGER	PK		AI	NN		internal trail unit specimen id
TrialUnitId	INTEGER		FK		NN		trial unit id
SpecimenId	INTEGER		FK		NN		id of the planted specimen
ItemId	INTEGER		FK				source item (e.g. seed bag) for this particular trial unit - having it here allows to use different seed bags for each planted specimen if this is a case, link is optional as the seed source may come from other places
PlantDate	DATE						date when specimen has been planted in the trial unit
HarvestDate	DATE						date when specimen has been harvested from the trial unit
HasDied	TINYINT					0	flag if specimen died
Notes	VARCHAR(254)						additional notes
SpecimenNumber	INTEGER				NN	0	distinguish between instances of the same specimen in the same trial unit - just number them
TUSLabel	VARCHAR(254)						label for trial unit specimen - anything to make label on individual
TUSBarcode	VARCHAR(254)						barcode for trial unit specimen - if exists must be unique - labelling individual specimen in trial unit

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`TrialUnitSpecimenId`
xtus_SpecimenId	INDEX	`SpecimenId`
xtus_PlantDate	INDEX	`PlantDate`
xtus_HarvestDate	INDEX	`HarvestDate`
xtus_ItemId	INDEX	`ItemId`
xtus_USN	UNIQUE	`SpecimenId`, `SpecimenNumber`, `TrialUnitId`
xtus_TUSBarcode	UNIQUE	`TUSBarcode`
xtus_TrialUnitId	INDEX	`TrialUnitId`

Table: trialtrait	Part of the trial definition. List of traits expected to be measured for a trial. This table should be rather treated as a control checkpoint, while inserting real measurements into samplemeasurement table.

ColName	Datatype	PK	FK	AI	NN	DefaultValue	Comments
TrialTraitId	INTEGER	PK		AI	NN		record id
UnitId	INTEGER		FK				Alternative unit used in trait measurement, optional, if not provided use unit attached to trait directly
TrialId	INTEGER		FK		NN		trial id
TraitId	INTEGER		FK		NN		trait id
Compulsory	TINYINT(1)				NN	0	0\|1 flag indicating that this combination is compulsory to measure in the trial

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`TrialTraitId`
xtt_TraitId	INDEX	`TraitId`
xtt_TrialId	INDEX	`TrialId`

Table: trialdimension	Optional definition of the trial dimensionality. If user wants to define that X dimension is called e.g. row and Y dimension is called column - this is a place to do it. In the future this can be extended to contain trial level information about e.g. plot width, plot height, plot spaces, etc. Trial and dimention combination has to be unique - forced by unique index.

ColName	Datatype	PK	FK	AI	NN	Comments
TrialDimensionId	INTEGER	PK		AI	NN	Dimension id
TrialId	INTEGER		FK		NN	trial id to which dimension refers to
Dimension	SET('X','Y','Z','EntryId','Position')				NN	which trial dimension it is
DimensionName	VARCHAR(100)				NN	name of the dimension e.g Y can be column or range or something else, EntryId can be PlotId or PlotNumber or else

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`TrialDimensionId`
xtd_TrialId	INDEX	`TrialId`
xtd_Dimension	UNIQUE	`TrialId`, `Dimension`
xtd_DimensionName	UNIQUE	`TrialId`, `DimensionName`

Table: trialunitkeyword	keywords for a trial unit

ColName	Datatype	PK	FK	AI	NN	Comments
TrialUnitKeywordId	INTEGER	PK		AI	NN	trial unit keyword id
TrialUnitId	INTEGER		FK		NN	trial unit id
KeywordId	INTEGER		FK		NN	keyword id

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`TrialUnitKeywordId`
tuk_KeywordId	INDEX	`KeywordId`
tuk_TrialUnitId	INDEX	`TrialUnitId`

Table: crossing	A table which stores crossing information for nursery management

ColName	Datatype	PK	FK	AI	NN	Comments
CrossingId	INTEGER	PK		AI	NN	Id for crossing record
TrialId	INTEGER		FK		NN	trial (nursery) for which the crossing is designed
BreedingMethodId	INTEGER		FK		NN	type of the cross defined by breeding method - has to be compatible with pedigree table
MaleParentId	INTEGER		FK		NN	id of the male parent - specimen in the trial unit
FemaleParentId	INTEGER		FK		NN	id of the female parent - specimen in the trial unit
CrossingDateTime	DATETIME					date time when crossing performed
UserId	INTEGER		FK		NN	who has done the cross
CrossingNote	VARCHAR(254)					some notes about the cross

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`CrossingId`
xcr_trialid	INDEX	`TrialId`
xcr_breedingmethodid	INDEX	`BreedingMethodId`
xcr_userid	INDEX	`UserId`
xcr_maleparentid	INDEX	`MaleParentId`
xcr_femaleparentid	INDEX	`FemaleParentId`

Table: trialunitfactor	Additional virtual columns for trial units.

ColName	Datatype	PK	FK	NN	Comments
TrialUnitId	INTEGER	PK	FK	NN	trial unit id
FactorId	INTEGER	PK	FK	NN	factor id
FactorValue	VARCHAR(255)			NN	value

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`TrialUnitId`, `FactorId`
FactorId	INDEX	`FactorId`

[BACK TO TOP]

Region: Virtual column definitions

Table: factor	Main database table containing the definitions of all virtual columns. A uniform mechanism across the database where all tables with the suffix factor relate to this table.

ColName	Datatype	PK	AI	NN	DefaultValue	Comments
FactorId	INTEGER	PK	AI	NN		factor id
FactorName	VARCHAR(32)			NN		column name
FactorCaption	VARCHAR(64)					caption (shorter version of name)
FactorDescription	VARCHAR(255)					what is stored in the column
TableNameOfFactor	VARCHAR(32)			NN		which main table this factor refers to
FactorDataType	VARCHAR(8)			NN		data type (e.g. VARCHAR)
CanFactorHaveNull	TINYINT(1)			NN		can value be null (0\|1)
FactorValueMaxLength	INTEGER(10)			NN		maximum size of value (e.g. 256) refers to the maximum length of FactorValue is VARCHAR in the factor data table like contactfactor
FactorUnit	VARCHAR(16)					value unit (e.g. kg, meters, etc)
OwnGroupId	INTEGER			NN		the group that owns this virtual column definition
Public	TINYINT			NN	0	if public=1, it means other group administrators can edit and delete this definition
FactorValidRule	VARCHAR(100)					factor value validation rule (optional)
FactorValidRuleErrMsg	VARCHAR(254)					error message if value does not conform to validation rule

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`FactorId`
xf_FactorName	INDEX	`FactorName`
xf_FactorCaption	INDEX	`FactorCaption`
xf_TableNameOfFactor	INDEX	`TableNameOfFactor`

Table: factoralias	Aliases for factors

ColName	Datatype	PK	FK	AI	NN	Comments
FactorAliasId	INTEGER	PK		AI	NN	factoralias id
FactorAliasName	VARCHAR(64)				NN	alternative name of the factor
FactorId	INTEGER		FK		NN	factor id

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`FactorAliasId`
xfa_FactorAliasName	INDEX	`FactorAliasName`
xfa_FactorId	INDEX	`FactorId`

[BACK TO TOP]

RELATIONS	list of model relations

RelName	RelType	FromTable	ToTable	CreateRefDef
Cont+ContFact	1..n	contact	contactfactor	1
Cont+SysUser	1..n (NA)	contact	systemuser	1
DesType+Trial	1..n (NA)	designtype	trial	1
Fac+Contact	1..n	factor	contactfactor	1
Fac+FacAlias	1..n (NA)	factor	factoralias	1
Fac+Genotype	1..n	factor	genotypefactor	1
Fac+Site	1..n	factor	sitefactor	1
Fac+Treat	1..n	factor	treatmentfactor	1
Fac+Trial	1..n	factor	trialfactor	1
Fac+TrialEvent	1..n	factor	trialeventfactor	1
Gen+GenA	1..n (NA)	genotype	genotypealias	1
Gen+GenFac	1..n	genotype	genotypefactor	1
Gen+GenTrait	1..n (NA)	genotype	genotypetrait	1
Genus+Gen	1..n (NA)	genus	genotype	1
Org+Cont	1..n (NA)	organisation	contact	1
Site+SiteF	1..n	site	sitefactor	1
SysGrp+ASysGrp	1..n (NA)	systemgroup	authorisedsystemgroup	1
SysUser+ActLog	1..n (NA)	systemuser	activitylog	1
SysUser+ASysGrp	1..n (NA)	systemuser	authorisedsystemgroup	1
Trait+GenTrait	1..n (NA)	trait	genotypetrait	1
Trait+TraitA	1..n (NA)	trait	traitalias	1
Treat+TreatF	1..n	treatment	treatmentfactor	1
Trial+TrailF	1..n	trial	trialfactor	1
Trial+TrialEvent	1..n (NA)	trial	trialevent	1
Trial+TrialUnit	1..n (NA)	trial	trialunit	1
TrialEvent+TrialEventF	1..n	trialevent	trialeventfactor	1
TUnit+SM	1..n	trialunit	samplemeasurement	1
Trait+SM	1..n (NA)	trait	samplemeasurement	1
Site+Trial	1..n (NA)	site	trial	1
Spec+GenSpec	1..n (NA)	specimen	genotypespecimen	1
Gen+GenSpec	1..n (NA)	genotype	genotypespecimen	1
TUnit+TUnitSpec	1..n (NA)	trialunit	trialunitspecimen	1
Spec+TUnitSpec	1..n (NA)	specimen	trialunitspecimen	1
SysUser+SM	1..n	systemuser	samplemeasurement	1
Spec+SpecF	1..n	specimen	specimenfactor	1
Fac+Spec	1..n	factor	specimenfactor	1
Cont+Trial	1..n (NA)	contact	trial	1
Cont+Site	1..n (NA)	contact	site	1
SpecGr+SpecGrEntry	1..n	specimengroup	specimengroupentry	1
Spec+SpecGrEntry	1..n	specimen	specimengroupentry	1
Spec+PedParent	1..n (NA)	specimen	pedigree	1
Spec+Ped	1..n (NA)	specimen	pedigree	1
BM+Spec	1..n (NA)	breedingmethod	specimen	1
Fac+BM	1..n (NA)	factor	breedingmethodfactor	1
BM+BMF	1..n (NA)	breedingmethod	breedingmethodfactor	1
Item+ItemF	1..n (NA)	item	itemfactor	1
Fac+Item	1..n (NA)	factor	itemfactor	1
Item+ItemGrEntry	1..n (NA)	item	itemgroupentry	1
ItemGr+ItemGrEntry	1..n (NA)	itemgroup	itemgroupentry	1
Spec+Item	1..n (NA)	specimen	item	1
ItemUnit+Item	1..n (NA)	generalunit	item	1
Stor+Item	1..n (NA)	storage	item	1
DevReg+DevRegF	1..n (NA)	deviceregister	deviceregisterfactor	1
Fac+DevReg	1..n (NA)	factor	deviceregisterfactor	1
DevReg+Item	1..n (NA)	deviceregister	item	1
SysUser+Item	1..n (NA)	systemuser	item	1
Stor+StorPar	1..n (NA)	storage	storage	1
Trait+TrialTrait	1..n (NA)	trait	trialtrait	1
Trial+TrialTrait	1..n (NA)	trial	trialtrait	1
ContainerType+Item	1..n (NA)	generaltype	item	1
Type+DevReg	1..n (NA)	generaltype	deviceregister	1
Type+Item	1..n (NA)	generaltype	item	1
Type+Site	1..n (NA)	generaltype	site	1
Type+Trial	1..n (NA)	generaltype	trial	1
Type+SM	1..n (NA)	generaltype	samplemeasurement	1
Type+TrialEvent	1..n (NA)	generaltype	trialevent	1
Fac+GenType	1..n (NA)	factor	generaltypefactor	1
GenType+GenTypeF	1..n (NA)	generaltype	generaltypefactor	1
SysUser+TrialEvent	1..n (NA)	systemuser	trialevent	1
Type+SpecGr	1..n (NA)	generaltype	specimengroup	1
Cont+Item	1..n (NA)	contact	item	1
StateType+Item	1..n (NA)	generaltype	item	1
Item+ItemPar	1..n (NA)	item	itemparent	1
Item+ItemParPar	1..n (NA)	item	itemparent	1
Type+Ped	1..n (NA)	generaltype	pedigree	1
Type+ItemParent	1..n (NA)	generaltype	itemparent	1
TUnit+SrcTUnit	1..n (NA)	trialunit	trialunit	1
Type+GenSpec	1..n (NA)	generaltype	genotypespecimen	1
TUnitSpec+Item	1..n (NA)	trialunitspecimen	item	1
Wflow+WflowDef	1..n (NA)	workflow	workflowdef	1
Type+Wflow	1..n (NA)	generaltype	workflow	1
Trial+TrialWflow	1..n (NA)	trial	trialworkflow	1
WflowDef+TWflow	1..n (NA)	workflowdef	trialworkflow	1
Wflow+Trial	1..n (NA)	workflow	trial	1
Project+Trial	1..n (NA)	project	trial	1
Type+Project	1..n (NA)	generaltype	project	1
Project+ProjectF	1..n	project	projectfactor	1
Fac+Project	1..n	factor	projectfactor	1
Cont+Project	1..n (NA)	contact	project	1
GenA+GenAFac	1..n (NA)	genotypealias	genotypealiasfactor	1
Fac+GenAF	1..n (NA)	factor	genotypealiasfactor	1
Type+Mmedia	1..n (NA)	generaltype	multimedia	1
SysUser+Multimedia	1..n (NA)	systemuser	multimedia	1
Item+ItemLog	1..n (NA)	item	itemlog	1
SysUser+ItemLog	1..n (NA)	systemuser	itemlog	1
Type+ItemLog	1..n (NA)	generaltype	itemlog	1
Item+TUnitSpec	1..n (NA)	item	trialunitspecimen	1
Org+OrgF	1..n (NA)	organisation	organisationfactor	1
Fac+Org	1..n (NA)	factor	organisationfactor	1
Type+GenA	1..n (NA)	generaltype	genotypealias	1
Gen+GenPedPar	1..n (NA)	genotype	genpedigree	1
Gen+GenPed	1..n (NA)	genotype	genpedigree	1
Type+GenPed	1..n (NA)	generaltype	genpedigree	1
TypeStat+GenA	1..n (NA)	generaltype	genotypealias	1
Unit+Trait	1..n (NA)	generalunit	trait	1
Unit+TrialEvent	1..n (NA)	generalunit	trialevent	1
Type+Trait	1..n (NA)	generaltype	trait	1
Type+Unit	1..n (NA)	generaltype	generalunit	1
MLT+MLTF	1..n (NA)	trialgroup	trialgroupfactor	1
Fac+TrialGrp	1..n (NA)	factor	trialgroupfactor	1
TrialGrp+TrialGrpEntry	1..n (NA)	trialgroup	trialgroupentry	1
Trial+MLT	1..n (NA)	trial	trialgroupentry	1
Type+TrialGrp	1..n (NA)	generaltype	trialgroup	1
SysUser+TrialGrp	1..n (NA)	systemuser	trialgroup	1
Type+SpecGrStat	1..n (NA)	generaltype	specimengroup	1
Type+BrMeth	1..n (NA)	generaltype	breedingmethod	1
Unit+TrialTrait	1..n (NA)	generalunit	trialtrait	1
Unit+FromUnit	1..n (NA)	generalunit	conversionrule	1
Unit+ToUnit	1..n (NA)	generalunit	conversionrule	1
Trial+TrialDim	1..n (NA)	trial	trialdimension	1
TUnitSpec+SM	1..n (NA)	trialunitspecimen	samplemeasurement	0
KeyG+Entry	1..n (NA)	keywordgroup	keywordgroupentry	1
Key+Entry	1..n (NA)	keyword	keywordgroupentry	1
Rel_125	1..n (NA)	trialunit	trialunitkeyword	1
TUnit+Key	1..n (NA)	keyword	trialunitkeyword	1
Rel_127	1..n (NA)	specimen	specimenkeyword	1
Spec+Key	1..n (NA)	keyword	specimenkeyword	1
Trial+Cross	1..n (NA)	trial	crossing	1
Cross+BrMeth	1..n (NA)	breedingmethod	crossing	1
Cross+MaleParent	1..n (NA)	trialunitspecimen	crossing	1
Cross+FemaleParent	1..n (NA)	trialunitspecimen	crossing	1
SysUser+Cross	1..n (NA)	systemuser	crossing	1
KeyGrp+User	1..n (NA)	systemuser	keywordgroup	1
TrGrp+TrGrpEnt	1..n (NA)	traitgroup	traitgroupentry	1
Trait+TrGrEnt	1..n (NA)	trait	traitgroupentry	1
TrGr+User	1..n (NA)	systemuser	traitgroup	1
SMG+SM	1..n (NA)	smgroup	samplemeasurement	0
Type+TraitData	1..n (NA)	generaltype	trait	1
Key+User	1..n (NA)	systemuser	keyword	1
Trial+SMG	1..n (NA)	trial	smgroup	1
User+SMG	1..n (NA)	systemuser	smgroup	1
Season+Trial	1..n (NA)	generaltype	trial	1
Genus+GAlias	1..n (NA)	genus	genotypealias	1
TUnit+Treatment	1..n (NA)	treatment	trialunittreatment	1
CMG+CM	1..n (NA)	cmgroup	crossingmeasurement	1
Trial+CMG	1..n (NA)	trial	cmgroup	1
Trait+CMG	1..n (NA)	trait	crossingmeasurement	1
User+CMG	1..n (NA)	systemuser	cmgroup	1
User+CM	1..n (NA)	systemuser	crossingmeasurement	1
Type+CM	1..n (NA)	generaltype	crossingmeasurement	1
Crossing+CM	1..n (NA)	crossing	crossingmeasurement	1
Survey+SurveryTUnit	1..n (NA)	survey	surveytrialunit	1
Survery+SurveyTrait	1..n (NA)	survey	surveytrait	1
Trait+SurveyTrait	1..n (NA)	trait	surveytrait	1
TUnitSpec+Pedigree	1..n (NA)	trialunitspecimen	pedigree	1
TUnit+SurveyTUnit	1..n (NA)	trialunit	surveytrialunit	1
Crossing+Spec	1..n (NA)	crossing	specimen	1
Item+IM	1..n (NA)	item	itemmeasurement	1
Trait+IM	1..n (NA)	trait	itemmeasurement	1
User+IM	1..n (NA)	systemuser	itemmeasurement	1
Type+IM	1..n (NA)	generaltype	itemmeasurement	1
IMG+IM	1..n (NA)	imgroup	itemmeasurement	1
TUnit+TUnitTreatment	1..n (NA)	trialunit	trialunittreatment	1
Cont+Survey	1..n (NA)	contact	survey	1
Survey+SurveyF	1..n (NA)	survey	surveyfactor	1
Fac+Survey	1..n (NA)	factor	surveyfactor	1
User+IMG	1..n (NA)	systemuser	imgroup	1
Survey+SM	1..n (NA)	survey	samplemeasurement	1
Get+Taxon	1..n (NA)	taxonomy	genotype	1
Tax+ParTax	1..n (NA)	taxonomy	taxonomy	1
TUnit+TUnitF	1..n	trialunit	trialunitfactor	1
Fac+TUnit	1..n	factor	trialunitfactor	1
Type+TUnit	1..n (NA)	generaltype	trialunit	1
Type+Survey	1..n (NA)	generaltype	survey	1
Contact+SurveyTUnit	1..n (NA)	contact	surveytrialunit	1