Concentrations data formats

Three schemes for data are implemented:

  1. 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. LOR), Analysis samples (e.g. analysis date) and Concentrations;

  2. SSD scheme: data according to the EFSA Standard Sample Description (SSD) guideline; SSD data are converted automatically to the MCRA scheme;

  3. 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.

Concentration data

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

Sample-based concentration data

This sub-group contains five tables to specify food samples, analytical methods, their properties for given substances, analyses and concentrations.

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 records, which record the substances that are measured by this method (and their limits of reporting).

Table aliases: AnalyticalMethod, AnalyticalMethods.

Table 46 Table definition for AnalyticalMethods.
Name Type Description Aliases Required

idAnalyticalMethod

AlphaNumeric(50)

The code for the method of analysis.

idAnalyticalMethod, AnalyticalMethodId, AnalyticalMethodName, Id

Yes

Description

AlphaNumeric(200)

Additional description of method of analysis.

Description

No

Analytical method properties for substances

Table aliases: AnalyticalMethodSubstances, AnalyticalMethodSubstance.

Table 47 Table definition for AnalyticalMethodCompounds.
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

LOR

Numeric

The limit of reporting (LOR). In MCRA, LOR just means the limit below which no quantitative result has been reported. Depending on a laboratory’s format of reporting, LOR may be a limit of detection (LOD), a limit of quantification (LOQ) or another limit.

LOR

Yes

ConcentrationUnit

ConcentrationUnits

The unit of the concentrations/LORs reported by the analytical method for this substance (default mg/kg).

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 48 Table definition for FoodSamples.
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

DateSampling

DateTime

The date of sampling.

DateSampling, SamplingDate

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 49 Table definition for SampleProperties.
Name Type Description Aliases Required

Name

AlphaNumeric(50)

The name of the property.

Id

Yes

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 50 Table definition for SamplePropertyValues.
Name Type Description Aliases Required

idSample

AlphaNumeric(50)

The identification number of the food sample.

Id

Yes

PropertyName

AlphaNumeric(50)

The name of the property.

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 51 Table definition for AnalysisSamples.
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

Sample concentrations

The positive concentration values for substances from analysis in the unit specified in table AnalysisSamples. Non-detects (i.e. results ‘less than LOR’) 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 52 Table definition for ConcentrationsPerSample.
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

Yes

Tabulated concentration data

Tabulated concentration data provide a simplified concentration data format, where samples and analytical methods are not explicitly specified and analysis results can be tabulated for repeats of the same outcome. This is a convenient data format for single-substance analyses, but it should be noted that it is not possible to use this data in sample-based methods of multiple substances, because it does not record co-occurrence information of substances in samples. Tabulated concentrations data is converted to the internal, relational data format of MCRA.

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. Non-detects (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 non-detect measurement), this value is recorded as the LOR (negated). All non-detect 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 non-detect measurement and the LOR is set as default to the value 1E-08.

Table aliases: ConcentrationTabulated, ConcentrationValues, TabulatedConcentrations, TabulatedConcentration.

Table 53 Table definition for ConcentrationTabulated.
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

ConcentrationUnits

The unit of the specified concentrations/LORs (default mg/kg).

ConcentrationUnit, Unit

No

EFSA SSD concentration data

MCRA provides an option to upload concentration data that is formatted according to the EFSA Standard Sample Description (SSD) guideline. SSD formatted concentrations data is converted to the internal, relational data format of MCRA.

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 54 Table definition for ConcentrationsSSD.
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

Yes

prodCode

AlphaNumeric(20)

Code identifying the food as measured. Should be equal to a code idFood in the Foods table.

prodCode

Yes

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

AlphaNumeric(3)

Type of residue data. Should be VAL, LOQ or LOD.

resType

Yes

Concentration distributions

Substance concentrations on foods specified in the form of summary statistics.

Table aliases: ConcentrationDistributions, ConcentrationDistribution.

Table 55 Table definition for ConcentrationDistributions.
Name Type Description Aliases Required

idFood

AlphaNumeric(50)

Food code, the raw agricultural commodity.

idFood

Yes

idSubstance

AlphaNumeric(50)

The code of the substance.

idSubstance, SubstanceId, SubstanceCode, Substance

Yes

Mean

Numeric

The mean of (monitoring) samples, on the original scale (in mg/kg).

Mean

Yes

CV

Numeric

Coefficient of variation, for samples of the size of the TDS pooled amount.

CV

No

Percentile

Numeric

The percentile at the point specified by the percentage.

Percentile

No

Percentage

Numeric

The percentage that belongs to the given the percentile, e.g., 95 (in mg/kg).

Percentage

No

Limit

Numeric

The specified norm value or limit value (in mg/kg).

Limit

No

ConcentrationUnit

ConcentrationUnits

The unit of the limit value (default mg/kg).

ConcentrationUnit, Unit

No