Concentrations data formats

Three schemes for data are implemented:

MCRA scheme: relational tables that can hold all information about Food samples (e.g. sampling date and location), Analytical methods, Analytical method properties for substances (e.g. LOQ and LOD), Analysis samples (e.g. analysis date) and Concentrations (e.g. concentration and residue type (positive, non-detect, non-quantification or missing));
SSD scheme: data according to the EFSA Standard Sample Description (SSD) guideline; SSD data are converted automatically to the MCRA scheme;
Tabulated data scheme: simplified data format, where samples and analytical methods are not explicitly specified. Tabulated concentration data are converted automatically to the MCRA scheme.

In this group all tables are collected that store information related to concentration or concentration related entities.

Relational concentration data format

The relational data format is the data format that is used internally in MCRA.

Download empty dataset template: Zipped CSV Excel

Analytical methods

The analytical methods used for analyzing the samples are recorded in the analytical methods table. Each analytical method should have a unique identification code (idAnalyticalMethod). The description field may be used for a more detailed description of the analytical method. The records of this table should be linked to one or more analytical method substance properties table, which record the substances that are measured by this method (and their limits of reporting).

Table aliases: AnalyticalMethod, AnalyticalMethods.

Table 48 Table definition for Analytical methods.
Name	Type	Description	Aliases	Required
idAnalyticalMethod	AlphaNumeric(50)	The code for the method of analysis.	idAnalyticalMethod, AnalyticalMethodId, AnalyticalMethodName, Id	Yes
Name	AlphaNumeric(100)	Name of the analytical method.	Name	No
Description	AlphaNumeric(255)	Additional description of method of analysis.	Description	No

Analytical method properties for substances

Table aliases: AnalyticalMethodSubstances, AnalyticalMethodSubstance, AnalyticalMethodCompounds, AnalyticalMethodCompound.

Table 49 Table definition for Analytical method properties for substances.
Name	Type	Description	Aliases	Required
idAnalyticalMethod	AlphaNumeric(50)	The code of method of analysis.	idAnalyticalMethod, AnalyticalMethodName, AnalyticalMethodId	Yes
idSubstance	AlphaNumeric(50)	The substance code.	idSubstance, SubstanceId, Substance	Yes
LOD	Numeric	The limit of detection (LOD).	LOD	No
LOQ	Numeric	The limit of quantification (LOQ).	LOQ, LOR	No
ConcentrationUnit	ConcentrationUnit	The code of the unit as used for substance concentration data. Allowed code: kg/kg or kilogram/kilogram; g/kg or gram/kilogram; mg/kg or milligram/kilogram (default); µg/kg or microgram/kilogram; ng/kg or nanogram/kilogram; pg/kg or picogram/kilogram.	ConcentrationUnit, Units, Unit	No

Food samples

Food sample for analysis of concentrations. May be characterised by location and/or date of sampling. A sample can be analysed multiple times, the results per analysis are stored as analysis samples.

Table aliases: FoodSamples, FoodSample, Samples, Sample, PrimarySample, PrimarySamples.

Table 50 Table definition for Food samples.
Name	Type	Description	Aliases	Required
idFoodSample	AlphaNumeric(50)	The identification number of the food sample.	idFoodSample, idSample, SampleId, Id	Yes
idFood	AlphaNumeric(50)	The food code.	idFood, FoodId, Food, FoodCode	Yes
Location	AlphaNumeric(50)	The location or country code, sampling location.	Location, LocationSampling, SamplingLocation, Country	No
Region	AlphaNumeric(50)	The area or region within the sampling location.	Region, Area, SamplingRegion, SamplingArea	No
DateSampling	DateTime	The date of sampling.	DateSampling, SamplingDate	No
ProductionMethod	AlphaNumeric(50)	Additional information on the type/method of production of the sampled food.	ProductionMethod, ProductionType	No
Name	AlphaNumeric(100)	Name of the food sample.	Name	No
Description	AlphaNumeric(200)	Additional description of the food sample.	Description	No

Sample properties

Food sample properties, additional columns that can also be specified as additional columns in the food samples table

Table aliases: SampleProperties, SampleProperty.

Table 56 Table definition for Sample properties.
Name	Type	Description	Aliases	Required
Name	AlphaNumeric(50)	The name of the property.	Id	Yes
Description	AlphaNumeric(200)	Additional description of the sample property.	Description	No

Sample property values

Food sample property values, additional columns that can also be specified as additional columns in the food samples table

Table aliases: SamplePropertyValues, SamplePropertyValue.

Table 57 Table definition for Sample property values.
Name	Type	Description	Aliases	Required
idSample	AlphaNumeric(50)	The identification number of the food sample.	Id, IdFoodSample	Yes
PropertyName	AlphaNumeric(50)	The name of the property.	IdProperty, Name	Yes
TextValue	AlphaNumeric(50)	The value of the property as text value.		No
DoubleValue	Numeric	The value of the property as number.		No

Sample Analyses

An analysis sample specifies the analysis of a sample by an analytical method. A sample can be analysed multiple times, the results per analysis are stored as analysis samples.

Table aliases: AnalysisSamples, AnalysisSample, SampleAnalysis, SampleAnalyses.

Table 53 Table definition for Sample Analyses.
Name	Type	Description	Aliases	Required
idSampleAnalysis	AlphaNumeric(50)	The identification number of the analysed sample.	id, idSampleAnalysis, SampleAnalysis, idAnalysisSample, AnalysisSampleId	Yes
idFoodSample	AlphaNumeric(50)	The identification number of the food sample.	idFoodSample, idSample, SampleId, Sample	Yes
idAnalyticalMethod	AlphaNumeric(50)	The code of method of analysis.	idAnalyticalMethod, AnalyticalMethodId	Yes
DateAnalysis	DateTime	The date of the analysis.	DateAnalysis, AnalysisDate, Date	No
Name	AlphaNumeric(100)	Name of the analysis sample.	Name	No
Description	AlphaNumeric(200)	Additional description of the the analysis sample.	Description	No

Sample concentrations

The positive concentration values for substances from analysis in the unit specified in table AnalysisSamples. Censored values (i.e. results ‘less than LOQ or LOD’) are not included, their existence can be inferred from the tables AnalysisSamples and AnalyticalMethodSubstances, and the LOR itself from the table AnalyticalMethods.

Table aliases: SampleConcentrations, ConcentrationsPerSample, ConcentrationPerSample.

Table 54 Table definition for Sample concentrations.
Name	Type	Description	Aliases	Required
idSampleAnalysis	AlphaNumeric(50)	The identification number of the analysed sample.	idSampleAnalysis, SampleAnalysis, idAnalysisSample, AnalysisSampleId	Yes
idSubstance	AlphaNumeric(50)	The substance code.	idSubstance, SubstanceId, Substance	Yes
Concentration	Numeric	The measured concentration.	Concentration	No
ResType	ResType	The type of residue. Should be VAL (= default), LOQ, LOD or MV.	ResType	No

SSD concentration data format

The Standard Sample Description (SSD) concentration is the standard data format proposed by EFSA. Optionally, additional sample properties may be specified of fields that are not part of the SSD format (e.g., season of sampling). For this, the sample properties table and the sample property values table can be used.

Download empty dataset template: Zipped CSV Excel

SSD concentrations

MCRA uses the concept of samples analysed by analytical methods, where the analytical method is characterised by the substances analysed and the LORs for these substances. However, the SSD data do not provide information on the analytical methods at this level of detail. Therefore, from the provided SSD records, analytical methods are reconstructed and samples are linked to these analytical methods. All SSD records with the same labSampCode and labSubSampCode are considered to be from the same sample. All SSD samples that have records for the same substances, with the same LOQ/LOD values and resUnit are considered to originate from the same reconstructed analytical method. If both LOQ and LOD are provided, LOQ is used as LOR of the reconstructed analytical method. It is highly recommended to supply LOQ/LOD values, even for positive measurement, because this reduces the number of reconstructed analytical methods.

Table aliases: ConcentrationsSSD, SSDConcentrations.

Table 55 Table definition for SSD concentrations.
Name	Type	Description	Aliases	Required
labSampCode	AlphaNumeric(30)	Code of the laboratory sample. MCRA will use the combination of labSampCode and labSubSampCode as unique code for a sample.	labSampCode	Yes
labSubSampCode	AlphaNumeric(4)	Code of the laboratory sub-sample. MCRA will use the combination of labSampCode and labSubSampCode as unique code for a sample.	labSubSampCode	No
sampCountry	AlphaNumeric(2)	Two-letter code to identify the country of sampling.	sampCountry	No
sampArea	AlphaNumeric(5)	Area where the sample was collected.	sampArea	No
prodCode	AlphaNumeric(20)	Code identifying the modelled food. Should be equal to a code idFood in the Foods table.	prodCode	Yes
prodProdMeth	AlphaNumeric(5)	Code providing additional information on the type of production for the food under analysis.	prodProdMeth	No
sampY	Integer(4)	Year of sampling.	sampY	Yes
sampM	Integer(2)	Month of sampling.	sampM	No
sampD	Integer(2)	Day of sampling.	sampD	No
analysisY	Integer(4)	Year of analysis.	analysisY	Yes
analysisM	Integer(2)	Month of analysis.	analysisM	No
analysisD	Integer(2)	Day of analysis.	analysisD	No
paramCode	AlphaNumeric(20)	Code identifying the substance.	paramCode	Yes
resUnit	AlphaNumeric(5)	Unit of residue measurement.	resUnit	Yes
resLOD	Numeric	Residue Limit Of Detection. Required if resType is LOD. MCRA will use resLOD as LOR if resLOQ is not provided.	resLOD	No
resLOQ	Numeric	Residue Limit Of Quantification. Required if resType is LOQ. MCRA will use resLOQ as LOR if provided.	resLOQ	No
resVal	Numeric	Required if resType is VAL.	resVal	No
resType	ResType	Type of residue data. Should be VAL, LOQ, LOD or MV.	resType	Yes

Sample properties

Food sample properties, additional columns that can also be specified as additional columns in the food samples table

Table aliases: SampleProperties, SampleProperty.

Table 56 Table definition for Sample properties.
Name	Type	Description	Aliases	Required
Name	AlphaNumeric(50)	The name of the property.	Id	Yes
Description	AlphaNumeric(200)	Additional description of the sample property.	Description	No

Sample property values

Food sample property values, additional columns that can also be specified as additional columns in the food samples table

Table aliases: SamplePropertyValues, SamplePropertyValue.

Table 57 Table definition for Sample property values.
Name	Type	Description	Aliases	Required
idSample	AlphaNumeric(50)	The identification number of the food sample.	Id, IdFoodSample	Yes
PropertyName	AlphaNumeric(50)	The name of the property.	IdProperty, Name	Yes
TextValue	AlphaNumeric(50)	The value of the property as text value.		No
DoubleValue	Numeric	The value of the property as number.		No

Tabulated concentration data format

The tabulated concentration data format is an old data format for entering concentration data.

Download empty dataset template: Zipped CSV Excel

Tabulated concentrations

In the tabulated concentration data table, each record represents one or multiple samples, and each sample contains a concentration value for a food/substance combination. Censored values (i.e. concentrations less than LOR) are specified as negative values, i.e. ‘less than LOR’ should be specified as minus the LOR value. MCRA uses the concept of samples analysed by analytical methods, where the analytical method is characterised by the substances analysed and the LORs for these substances. However, the tabulated data do not provide this information explicitly. Samples are reconstructed from the tabulated records using the NumberOfSamples field to create that number of single substance samples. Analytical methods are reconstructed from the data, with each analytical method having only one analysed substance with a LOR and concentration unit. When a negative concentration value is given (i.e., it is a censored measurement), this value is recorded as the LOR (negated). All censored measurements of the same substance with the same LOR and concentration unit are linked to the same analytical method. When a positive concentration value is given, this value is recorded as the measured concentration of the sample. All positive measurements of the same substance are linked to the same analytical method that has an artificial LOR that is smaller than the lowest positive concentration. When a concentration of 0 (zero) is given, the measurement is considered to be a censored measurement and the LOR is set as default to the value 1E-08.

Table aliases: ConcentrationTabulated, ConcentrationValues, TabulatedConcentrations, TabulatedConcentration.

Table 58 Table definition for Tabulated concentrations.
Name	Type	Description	Aliases	Required
GUID	AlphaNumeric(50)	Unique identifier of the analysis sample of this tabulated concentration record.	idAnalysisSample, SampleId, SampleCode, Code, Id	No
idSubstance	AlphaNumeric(50)	The code of the substance of this concentration value.	idSubstance, SubstanceId, Substance	Yes
idFood	AlphaNumeric(50)	The food code.	idFood, FoodId, FoodMeasured, Food	Yes
DateSampling	AlphaNumeric(10)	The date of sampling.	DateSampling	No
SamplingType	AlphaNumeric(50)	The type of sampling (monitoring).	SamplingType	No
Location	AlphaNumeric(50)	The location or country of sampling.	Location, Country	No
NumberOfSamples	Integer	The count of the number of times the specified concentration or limit of reporting (LOR) occurs.	NumberOfSamples	Yes
Concentration	Numeric	The concentration or LOR. LORs are specified using a minus (-) sign.	Concentration, Value	Yes
ConcentrationUnit	ConcentrationUnit	The unit of the specified concentrations/LORs (default mg/kg).	ConcentrationUnit, Unit	No