Concentrations data formats
Concentrations data are analytical measurements of chemical substances occurring in food samples. The following data formats are supported/available for providing concentration data in MCRA:
Relational concentration data format: Concentration data is provided using a number of relational tables describing (food) samples (e.g., sampling date and location), the analytical methods with their properties for substances (e.g., LOQ and LOD), sample analyses describing the analyses performed on the samples (e.g., analysis date and the used analytical method), and concentration measurements (e.g., the measurement type and value). This relational table structure is also used internally by MCRA.
EFSA Standard Sample Description (SSD) format: Concentration data according to the EFSA Standard Sample Description (SSD) format. During upload, SSD data are converted automatically to the relational data structure used internally by MCRA.
Tabulated data scheme: This is a simplified data format where samples and analytical methods are not explicitly specified. Tabulated concentration data are converted automatically to the MCRA scheme.
Relational concentration data format
The relational data format is the data format that is used internally in MCRA.
Download empty dataset template:
Analytical methods
The analytical methods used for analysing 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).
Accepted table names: AnalyticalMethod, AnalyticalMethods.
| 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
This table describes the substances analysed by an analytical method. For each substance analysed by an analytical method a record should be included that describes the unit of measurement and the reporting limits (LOQ/LOD).
Accepted table names: AnalyticalMethodSubstances, AnalyticalMethodSubstance, AnalyticalMethodCompounds, AnalyticalMethodCompound.
| 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) is the lowest concentration of an substance in a sample that can be consistently detected. |
LOD |
No |
LOQ |
Numeric |
The limit of quantification (LOQ) is the lowest concentration of a substance that can be quantified. The LOQ should be larger than the LOD. |
LOQ, LOR |
No |
ConcentrationUnit |
ConcentrationUnit | The unit used for reporting the LOD, LOQ, and the substance concentrations. When not specified, then a default unit of mg/kg is assumed. |
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.
Accepted table names: FoodSamples, FoodSample, Samples, Sample, PrimarySample, PrimarySamples.
| 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
Accepted table names: SampleProperties, SampleProperty.
| 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
Accepted table names: SamplePropertyValues, SamplePropertyValue.
| 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.
Accepted table names: AnalysisSamples, AnalysisSample, SampleAnalysis, SampleAnalyses.
| 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
This table contains substance concentration values specified in the unit defined by the analytical method. The analytical method contains the list of all substances which have been analyzed in the analysis sample. This ConcentrationsPerSample table contains the analysis results where substances with positive concentrations are included. Censored values (i.e. results ‘less than LOQ or LOD’) are reported as follows: 1) Substances for which an LOD (Limit of detection) is reported are included with ResType ‘LOD’, without a concentration value. 2) Substances for which only an LOQ (Limit of quantification) is reported are EXCLUDED, because the LOQ value from the analytical method substances table (AnalyticalMethodCompounds) is used by default. Explicitly missing concentration values are specified with ResType ‘MV’ (obligatory).
Accepted table names: SampleConcentrations, ConcentrationsPerSample, ConcentrationPerSample.
| 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:
SSD concentrations
MCRA uses the concept of samples analysed by analytical methods, where the analytical method contains the substances analysed and the LOQs and LODs for these substances. However, the SSD data do not provide information on the analytical methods at this level of detail. Therefore, the provided SSD sample records are used to generate analytical methods which are linked to the samples. All SSD records with the same labSampCode and labSubSampCode compose one MCRA analysis sample. All SSD samples that contain the same substance, LOQ/LOD values and resUnit combinations are linked to the corresponding generated analytical method. If both LOQ and LOD are provided, the LOQ is used as the LOR of the generated analytical method. It is highly recommended to supply LOQ/LOD values, even for positive measurements, because this reduces the number of gererated analytical methods.
Accepted table names: ConcentrationsSSD, SSDConcentrations.
| 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 (50) |
Code identifying the modelled food. Should be equal to a code idFood in the Foods table. |
prodCode |
Yes |
prodProdMeth |
AlphaNumeric (50) |
Code providing additional information on the type of production for the food under analysis. |
prodProdMeth |
No |
sampY |
Integer (4) |
Year of sampling. |
sampY |
No |
sampM |
Integer (2) |
Month of sampling. |
sampM |
No |
sampD |
Integer (2) |
Day of sampling. |
sampD |
No |
analysisY |
Integer (4) |
Year of analysis. |
analysisY |
No |
analysisM |
Integer (2) |
Month of analysis. |
analysisM |
No |
analysisD |
Integer (2) |
Day of analysis. |
analysisD |
No |
paramCode |
AlphaNumeric (50) |
Code identifying the substance. |
paramCode |
Yes |
resUnit |
ConcentrationUnit | 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
Accepted table names: SampleProperties, SampleProperty.
| 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
Accepted table names: SamplePropertyValues, SamplePropertyValue.
| 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:
Tabulated concentrations
In the tabulated concentrations 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 to the default value 1E-08.
Accepted table names: ConcentrationTabulated, ConcentrationValues, TabulatedConcentrations, TabulatedConcentration.
| 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 |