DArTdb database model description

	Marker ER diagram Version: 2.7.0 Maker module of KDDart. This module is implemented in MonetDB, so relevant sql has to be generated.

[BACK TO TOP]

Region: Analysis definitions and results

Table: analysisgroup	Table for grouping extracts into an analytical unit or group. Potentially a population (parents + progeny) or other arbitrary grouping for which extracts have been, or will be, created to run genotypic analysis. The analysisgroup could equate to a shipment although the group only relates to a single type of marker. For additional marker types the analysisgroup can replicated to reference the separate marker type results. Combining data from various analysisgroups into another subset is possible, but requires analytical process.

Table: analysisgroup

Table for grouping extracts into an analytical unit or group. Potentially a population (parents + progeny) or other arbitrary grouping for which extracts have been, or will be, created to run genotypic analysis. The analysisgroup could equate to a shipment although the group only relates to a single type of marker. For additional marker types the analysisgroup can replicated to reference the separate marker type results. Combining data from various analysisgroups into another subset is possible, but requires analytical process.

ColName	Datatype	PK	AI	NN	DefaultValue	Comments
AnalysisGroupId	INTEGER	PK	AI	NN		id of the group
AnalysisGroupName	VARCHAR(100)			NN		name of the group
AnalysisGroupDescription	VARCHAR(254)					description for the group
ContactId	INTEGER					(FK) link to contact, who can be analyst, data owner or analysis manager
DateCreated	DATETIME					creation time of the analysis group
DateUpdated	DATETIME					last update date time of the analysis group
AnalysisGroupMetaData	TEXT					Meta data for analysis group in JSON serialize format
OwnGroupId	INTEGER			NN		group id which owns the record
AccessGroupId	INTEGER			NN	0	group id which can access the record (different than owngroup)
OwnGroupPerm	TINYINT			NN		permission for the own group members
AccessGroupPerm	TINYINT			NN	0	permission for other group members
OtherPerm	TINYINT			NN	0	permission for all other system users

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`AnalysisGroupId`
xag_AnalysisGroupName	UNIQUE	`AnalysisGroupName`
xag_OwnGroupId	INDEX	`OwnGroupId`
xag_AccessGroupId	INDEX	`AccessGroupId`
xag_OwnGroupPerm	INDEX	`OwnGroupPerm`
xag_AccessGroupPerm	INDEX	`AccessGroupPerm`
xag_OtherPerm	INDEX	`OtherPerm`
xag_DateCreated	INDEX	`DateCreated`
xag_DateUpdated	INDEX	`DateUpdated`

Table: analysisgroupfactor	Additional virtual columns for analysisgroup.

ColName	Datatype	PK	FK	NN	Comments
AnalysisGroupId	INTEGER	PK	FK	NN	analysis group id
FactorId	INTEGER	PK		NN	(FK) id of factor column
FactorValue	VARCHAR(254)			NN	factor value

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`AnalysisGroupId`, `FactorId`
xagf_FactorId	INDEX	`FactorId`

[BACK TO TOP]

Region: Extract Data	Data for samples and subsamples

Table: extractdata	Each extract may have a several individual subsamples that has been processed (sequenced) for it and delivered as data points or data files. This entity is a register of those sub(samples) and individual data files delivered with them.

ColName	Datatype	PK	FK	AI	NN	Comments
ExtractDataId	INTEGER	PK		AI	NN	Internal extract data id
AnalGroupExtractId	INTEGER		FK		NN	Extract in analysis group for which data are being imported
OperatorId	INTEGER				NN	(FK) system user who created the record
DateCreated	DATETIME				NN	date when record was created
ExternalId	VARCHAR(254)					external identification - could be target id from DArT or any other unique identifier of extract sample as the reference to the external source
ExtractDataMeta	TEXT					meta information about extract data record - json object

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`ExtractDataId`
xed_OperatorId	INDEX	`OperatorId`
xed_DateCreated	INDEX	`DateCreated`
xed_ExternalId	INDEX	`ExternalId`
xed_AnalGroupExtractId	INDEX	`AnalGroupExtractId`

Table: extractdatafile	Data file for extract data record.

ColName	Datatype	PK	FK	AI	NN	Comments
ExtractDataFileId	INTEGER	PK		AI	NN	internal extract data file id
ExtractDataId	INTEGER		FK		NN	extract data record that file refers to
OperatorId	INTEGER				NN	(FK) operator id - who uploaded or created the file
FileType	INTEGER				NN	(FK) file type - should be a controlled set of known file types from general type - to start with - fastq, fastqcol, fasta, vcf
UploadTime	DATETIME				NN	time of the file upload
FileExtension	VARCHAR(10)					file extension
FileMeta	TEXT					meta data about the file - json object storing potentially file size, md5 sum, compression algorithm, etc.

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`ExtractDataFileId`
xedf_ExtractDataId	INDEX	`ExtractDataId`
xedf_OperatorId	INDEX	`OperatorId`
xedf_FileType	INDEX	`FileType`
xedf_UploadTime	INDEX	`UploadTime`
xedf_FileExtension	INDEX	`FileExtension`

[BACK TO TOP]

Region: Extracts

Table: extract	Store of DNA extracts created from plant samples. Inventory of samples resides in the item table and a group of items is a source for DNA extract.

ColName	Datatype	PK	FK	AI	NN	Comments
ExtractId	INTEGER	PK		AI	NN	DNA extract id
ParentExtractId	INTEGER		FK			id of the parent extract (e.g. aliquote from another well)
PlateId	INTEGER		FK			plate id
ItemGroupId	INTEGER					(FK) to itemgroup table from the core database, which could be a single or group of samples, from which the extract has been derived
GenotypeId	INTEGER					(FK) optional genotype id in case specimen has more than one genotype assigned
Tissue	INTEGER				NN	(FK) name of the tissue from which DNA has been extracted, class tissue in generaltype table
WellRow	VARCHAR(4)					optional information about well row position in the plate
WellCol	VARCHAR(4)					optional information about well col position in the plate
Quality	VARCHAR(30)					Quality description of this particular extract
Status	VARCHAR(30)					Status flag for this extract (e.g. dont use, old, usedup, etc)

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`ExtractId`
xpe_PlateId	INDEX	`PlateId`
xpe_ItemGroupId	INDEX	`ItemGroupId`
xpe_GenotypeId	INDEX	`GenotypeId`
xpe_ParentExtractId	INDEX	`ParentExtractId`
xpe_Tissue	INDEX	`Tissue`

Table: plate	Table to group extracts into plates. A group of plates can be a shipment unit.

ColName	Datatype	PK	AI	NN	Comments
PlateId	INTEGER	PK	AI	NN	plate internal id
PlateName	VARCHAR(60)			NN	can be a barcode or some arbitrary name
DateCreated	DATETIME			NN	date when plate was created
OperatorId	INTEGER			NN	(FK) to system user, who created the plate in the system
PlateType	INTEGER				(FK) type of plate (from general type class plate)
PlateDescription	VARCHAR(254)				some text describing it
StorageId	INTEGER				(FK) to storage table in core database
PlateWells	INTEGER				Number of wells in the plate (will determine valid row and column names)
PlateStatus	VARCHAR(100)				Status (like destroyed, master copy, shipped for genotyping, etc)
PlateMetaData	TEXT				Meta data for plate in JSON serialized format

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`PlateId`
xp_PlateName	UNIQUE	`PlateName`
xp_StorageId	INDEX	`StorageId`
xp_OperatorId	INDEX	`OperatorId`
xp_PlateType	INDEX	`PlateType`
xp_DateCreated	INDEX	`DateCreated`

Table: extractfactor	Additional virtual columns for extracts.

ColName	Datatype	PK	FK	NN	Comments
ExtractId	INTEGER	PK	FK	NN	Extract id
FactorId	INTEGER	PK		NN	factor column id
FactorValue	VARCHAR(254)			NN	factor value

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`ExtractId`, `FactorId`
xdef_FactorId	INDEX	`FactorId`

Table: platefactor	Additional virtual columns for plates.

ColName	Datatype	PK	FK	NN	Comments
PlateId	INTEGER	PK	FK	NN	plate id
FactorId	INTEGER	PK		NN	(FK) factor column id
FactorValue	VARCHAR(254)			NN	factor value

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`PlateId`, `FactorId`
xpf_FactorId	INDEX	`FactorId`

Table: analgroupextract	Maintains the many to many relationship between analysisgroup and DNA extracts, e.g. to group one shipment for genotyping. Combining various subsets of analysisgroup for analytical purposes can be done on request from existing imported data. Analysisgroup here is in fact a unit of data import.

ColName	Datatype	PK	FK	AI	NN	Comments
AnalGroupExtractId	INTEGER	PK		AI	NN	internal id
ExtractId	INTEGER		FK		NN	extract id
AnalysisGroupId	INTEGER		FK		NN	analysis group id - associated with dna extract(s)

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`AnalGroupExtractId`
xagde_analysisgroupid	INDEX	`AnalysisGroupId`
xagde_extractid	INDEX	`ExtractId`

[BACK TO TOP]

Region: Mapping	Genetic and physical mapping

Table: markermap	Storing physical or genetic map data. Maps can be created by genetic mapping software or alignment software.

ColName	Datatype	PK	AI	NN	Comments
MarkerMapId	INTEGER	PK	AI	NN	internal map id
MapName	VARCHAR(254)			NN	name of the map
MapType	INTEGER			NN	(FK) to generaltype table class genmap - type of the map (e.g. physical, genetic, consensus, etc)
OperatorId	INTEGER			NN	(FK) to systemuser - user who created the map
ModelRef	TEXT				model reference info - for physical maps
MapDescription	TEXT				general description of the map
MapSoftware	VARCHAR(254)				software (version) used to create the map
MapParameters	TEXT				map parameters (also software parameters) used for creation

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`MarkerMapId`
xmm_MapName	UNIQUE	`MapName`
xmm_MapType	INDEX	`MapType`
xmm_OperatorId	INDEX	`OperatorId`

Table: markermapgroup	Grouping maps (e.g. when creating consensus map and using other maps as a source of data)

ColName	Datatype	PK	FK	AI	NN	Comments
MarkerMapGroupId	INTEGER	PK		AI	NN	internal id
GroupName	VARCHAR(254)				NN	group name
ChildMapId	INTEGER		FK		NN	id of the map, which is a part of the group
ParentMapId	INTEGER		FK		NN	id of the map, which is the parent for other maps

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`MarkerMapGroupId`
xmmg_ChildMapId	INDEX	`ChildMapId`
xmmg_ParentMapId	INDEX	`ParentMapId`
xmmg_GroupName	UNIQUE	`GroupName`

Table: markermapposition	Markers positions in the map. We may link to marker table to retrive names, etc. but we also may store marker name in case we create e.g. consensus map and we are refering to many marker records of course named the same way.

ColName	Datatype	PK	FK	AI	NN	Comments
MarkerMapPositionId	INTEGER	PK		AI	NN	internal id
AnalysisGroupMarkerId	INTEGER		FK			analysis group marker id - if linked with particular marker
MarkerMapId	INTEGER		FK		NN	which map this entry belongs to
MarkerName	VARCHAR(60)					optional marker name in case record is not linked to analysisgroupmarker table (e.g. for consensus map)
ContigName	VARCHAR(60)				NN	name of contig (chromosome, linkage group, scaffold, etc)
ContigPosition	VARCHAR(60)				NN	genetic of physical position on contig

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`MarkerMapPositionId`
xagmm_MarkerMapId	INDEX	`MarkerMapId`
xagmm_MarkerName	INDEX	`MarkerName`
xagmm_ContigName	INDEX	`ContigName`
xagmm_ContigPosition	INDEX	`ContigPosition`
xagmm_AnalysisGroupMarkerId	INDEX	`AnalysisGroupMarkerId`

[BACK TO TOP]

Region: Markers

Table: analysisgroupmarker	Maintains the many to many relationship between an AnalysisGroup and markers. Marker quality and metadata can be stored in factor table, but perhaps better mechanism should be invented for per marker values.

ColName	Datatype	PK	FK	AI	NN	Comments
AnalysisGroupMarkerId	INTEGER	PK		AI	NN	internal id
DataSetId	INTEGER		FK		NN	data set id, where marker belongs to
MarkerName	VARCHAR(60)				NN	name of the marker
MarkerSequence	TEXT					sequence of the marker [e.g. ACGT]
MarkerDescription	VARCHAR(254)					can be any additional information about the marker like e.g. SNP position (A\gT:30) or SSR primer or marker method description, etc
MarkerExtRef	TEXT					URL or other external reference where additional marker info can be found e.g. link to NCBI

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`AnalysisGroupMarkerId`
xagm_MarkerName	INDEX	`MarkerName`
xagm_DataSetId	INDEX	`DataSetId`

Table: markeralias	Alias names for markers - if they are known under various naming conventions.

ColName	Datatype	PK	FK	AI	NN	Comments
MarkerAliasId	INTEGER	PK		AI	NN	internal id
AnalysisGroupMarkerId	INTEGER		FK		NN	original marker id
MarkerAliasName	VARCHAR(60)				NN	string with marker alias

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`MarkerAliasId`
xma_AliasName	INDEX	`MarkerAliasName`
xma_AnalysisGroupMarkerId	INDEX	`AnalysisGroupMarkerId`

[BACK TO TOP]

Region: Scoring and meta data for markers

Table: dataset	Data set for analysis group. New e.g. import for set (subset) of samples providing new set of markers. Markers are not restricted . Any set can be imported.

ColName	Datatype	PK	FK	AI	NN	Comments
DataSetId	INTEGER	PK		AI	NN	internal id
AnalysisGroupId	INTEGER		FK		NN	analysis group id
MarkerNameFieldName	VARCHAR(254)					field name of the marker name in the actual data set table e.g. CloneId
MarkerSequenceFieldName	VARCHAR(254)					field name of the marker sequence in the actual data set table e.g. Sequence
ParentDataSetId	INTEGER		FK			parent data set to which this data set is complement
DataSetType	INTEGER				NN	type of data set e.g. scoring, count or other quality data
DateCreated	DATETIME					date time when dataset was created
DateUpdated	DATETIME					date time when dataset was last updated
Description	VARCHAR(254)					optional description of the data set

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`DataSetId`
xds_AnalysisGroupId	INDEX	`AnalysisGroupId`
xds_ParentDataSetId	INDEX	`ParentDataSetId`
xds_DateCreated	INDEX	`DateCreated`
xds_DateUpdated	INDEX	`DateUpdated`

Table: datasetmarkermeta	Metadata fields attached to the dataset.

ColName	Datatype	PK	FK	AI	NN	Comments
DataSetMarkerMetaId	INTEGER	PK		AI	NN	internal id
DataSetId	INTEGER		FK			data set id
FactorId	INTEGER					factor id - link to factor table in the core module
FieldName	VARCHAR(254)					field name of the meta data in the actual data set table

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`DataSetMarkerMetaId`
xdsmm_DataSetId	INDEX	`DataSetId`
xdsmm_FactorId	INDEX	`FactorId`

Table: datasetextract	Actual list of samples in imported dataset. Should be the same or the subset of analysisgroup samples.

ColName	Datatype	PK	FK	AI	NN	Comments
DataSetExtractId	INTEGER	PK		AI	NN	internal id
DataSetId	INTEGER		FK			data set id
AnalGroupExtractId	INTEGER		FK			analysis group extract id
FieldName	VARCHAR(254)					field name for the extract in the actual data set table

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`DataSetExtractId`
xdse_DataSetId	INDEX	`DataSetId`
xdse_AnalGroupExtractId	INDEX	`AnalGroupExtractId`

[BACK TO TOP]

Region: TableComments	Table with field comments

Table: fieldcomment	Source of comments for the fields and tables - as MonetDB does not support it as mysql or postgresql, they are stored in this table and DAL use them as a source for help operations

ColName	Datatype	PK	NN	Comments
TableName	VARCHAR(254)	PK	NN	name of the table
FieldName	VARCHAR(254)	PK	NN	name of the field
FieldComment	TEXT		NN	comment for the field in the table
PrimaryKey	TINYINT		NN	[0\|1] flag if field is primary key

IndexName	IndexType	Columns
PRIMARY	PRIMARY KEY	`TableName`, `FieldName`

[BACK TO TOP]

RELATIONS	list of model relations

RelName	RelType	FromTable	ToTable	CreateRefDef
Plate+Ext	1..n (NA)	plate	extract	1
Ext+ExtF	1..n (NA)	extract	extractfactor	1
Plate+PlateF	1..n (NA)	plate	platefactor	1
AnGrp+AnGrpF	1..n (NA)	analysisgroup	analysisgroupfactor	1
AnGrp+AnGrpExtract	1..n (NA)	analysisgroup	analgroupextract	1
Ext+AnGrpExt	1..n (NA)	extract	analgroupextract	1
AnGrpMark+MarkA	1..n (NA)	analysisgroupmarker	markeralias	1
Ext+ExtPar	1..n (NA)	extract	extract	0
MarkMap+ChildMap	1..n (NA)	markermap	markermapgroup	1
MarkMap+ParMap	1..n (NA)	markermap	markermapgroup	0
MarkMap+MarkMapPos	1..n (NA)	markermap	markermapposition	0
DataSet+DataSetMarker	1..n (NA)	dataset	analysisgroupmarker	1
AnalGrp+DataSet	1..n (NA)	analysisgroup	dataset	1
DataSet+MarkerMeta	1..n (NA)	dataset	datasetmarkermeta	1
DataSet+DataSetExtract	1..n (NA)	dataset	datasetextract	1
AnalGrpExt+DataSetExt	1..n (NA)	analgroupextract	datasetextract	0
DataSet+Parent	1..n (NA)	dataset	dataset	0
AnGrpMark+MarkMapPos	1..n (NA)	analysisgroupmarker	markermapposition	0
ExtData+ExtDataFile	1..n (NA)	extractdata	extractdatafile	0
AnGrpExt+ExtData	1..n (NA)	analgroupextract	extractdata	1