A Database Schema for Standardized Data and Metadata Collection in Agricultural Experiments

Abstract

In large-scale, multi-national research projects on agricultural cropping systems such as SoilCare (Horizon 2020), ensuring consistency, comparability, and timely reporting of the (meta)data of the agricultural experiments across diverse partners has been a persistent challenge. To address these concerns, the SoilCare project developed a comprehensive data management system centered around a standardized template for the collection, organization, and sharing of experimental data and metadata from cropping systems. This template, designed to support harmonized sharing, analysis, and documentation through a common structure and terminology, meets the interdisciplinary requirements of modern agricultural research. Experimental data and metadata were structured into five core pools: 1. basic information, 2. field information, 3. experimental setup, 4. agricultural management data and 5. measured data/results. The Excel-based template was carefully structured to support integration into a relational database, enabling robust monitoring, analysis, and traceability of experimental processes and outcomes. The database schema and template, together with the (meta)data collected using this system during the SoilCare projects, were made openly available via Zenodo. The standardized approach ultimately enabled unified analyses and harmonized reporting across all experimental sites, demonstrating the system’s effectiveness in facilitating collaborative agricultural research at scale.

1. Introduction

Agricultural research increasingly addresses complex questions involving interacting processes across multiple sectors, spatial scales, and disciplines. Field experiments are often conducted under diverse soil, climatic, and crop management conditions. The interplay between tillage practices, (organic) amendments, crop rotations, and long-term soil fertility is especially critical, requiring coordinated and multi-faceted studies to fully understand system dynamics. Meta-analyses are frequently used to synthesize findings across studies [1,2,3,4]. While such analyses provide valuable insights, it is widely recommended to base them on original raw data rather than relying solely on published summaries and interpretations [5,6]. However, a recurring limitation is that key aspects and confounding parameters—such as details on the management practices, soil characteristics, and climate conditions—are not systematically reported across publications. In addition, practical challenges such as the difficulty of providing full datasets, the omission of certain data, and copyright restrictions further limit comparability and the potential for in-depth analyses.

In large international projects like the EU H2020 SoilCare project [7], multiple partners across different countries conducted coordinated field experiments to address shared research objectives on the effects of the different cropping systems on soil quality. Such efforts generate extensive datasets, often stored in varied file formats, locations, or even informal sources such as field notebooks or personal memory [8]. In long-term experiments, this information is particularly vulnerable—files may become inaccessible due to outdated formats, critical contextual details may be forgotten, and inconsistent reporting can hamper reuse. Moreover, the original experimental objectives may evolve, but legacy data often remains relevant for modern challenges.

Collaborative, interdisciplinary projects like SoilCare frequently disseminate results through diverse scientific publications and reports. To support this, researchers must share, combine, and analyze data across teams and disciplines. Without standardized formats and complete metadata, this leads to inconsistencies—such as unclear units, missing methodological information, or fragmented views of the experimental system—ultimately risking misinterpretation and analysis errors.

In this context, the adoption of a unified database structure with standardized terminology and controlled vocabularies is essential to ensure data quality, traceability, and long-term usability, as required by current open-access policies [9] and research ethics on FAIR data [10]. Data provenance—knowing the origin, context, and processing history of data—is equally critical [11]. Common database templates and open sharing frameworks allow partners to collaboratively prepare internal reports and publications during the project, while facilitating future open access for the broader scientific community.

The need for structured approaches to organizing agricultural data is well recognized. Over the past decades, several databases have been developed for specific purposes—for example, EuroSOMNET [12], LUCAS [13], and OpenSoil [14] for soil surveys, or databases supporting plant breeding and conservation practices [15,16,17,18]. More recently, initiatives such as the BonaRes LTE metadata repository [19] and the European LTE database [20] have systematized long-term field experiment metadata or focused on specific soil properties such as soil organic carbon [21]. These initiatives provide valuable frameworks, but they remain limited with respect to coordinated, multi-partner agricultural field experiments, where both data and metadata must be shared and reused by researchers from diverse backgrounds. For example, EuroSOMNET and LUCAS primarily focus on soil survey data and lack detailed representation of experimental management operations such as tillage or crop rotations. BonaRes offers a powerful repository for long-term experiment metadata, but it is not optimized for harmonizing raw experimental data and operational details across different projects and partners.

International initiatives such as IBSNAT [22] and the International Consortium for Agricultural Systems Applications (ICASA) [23,24] laid early foundations for standardized data documentation, especially for crop, water, and nutrient modeling. However, these standards are largely model-oriented and less accessible to heterogeneous research teams, and they do not readily accommodate the level of detail needed for documenting complex experimental operations. Resources such as the FAO AGROVOC thesaurus and WRB/USDA soil classification systems [25] also play an important role in enabling structured and comparable agricultural data.

Building on these efforts, the present work introduces a comprehensive database schema template following a relational database structure for monitoring agricultural experiments using the existing ICASA [24] and other standards when possible. The framework supports the systematic collection of both metadata and raw field data, ensuring reproducibility, comparability, and long-term accessibility. Data acquisition is facilitated through a user-friendly spreadsheet interface, while a relational database underlies the system to enable seamless conversion into a fully functional database. This design allows for the harmonized sharing of experimental details, operational procedures, and contextual information across multi-site contexts, reducing the dependence on logbooks, textual reports, or direct consultation with experiment managers. In this way, the proposed template bridges the gap between survey-based databases and model-oriented databases, while emphasizing usability and interoperability for interdisciplinary research teams.

2. Materials and Methods

2.1. Description of Existing Conditions and Data Needs

The SoilCare team examined the ICASA standards [24,26] as a potential basis for storing experimental data before the statistical analysis. It is important to note that ICASA does not provide a ready-made database, but rather a set of data standards and definitions developed primarily to support crop growth simulation models. While these standards offer a well-structured and widely recognized framework, particularly through their look-up tables and definitions of variables, they were not fully suited to the objectives of SoilCare.

The SoilCare project was not solely focused on generating calibration and validation data for crop modeling. Instead, it aimed to capture, store, and analyze experimental data on diverse cropping systems [27,28] with an emphasis on their impact on soil quality. Many ICASA parameters are crop-model-specific and therefore redundant for this purpose, while other important aspects of field experimentation, such as distinguishing experimental operations from routine field operations, were not clearly addressed.

To meet these broader requirements, we developed a database schema inspired by and closely aligned with ICASA standards. We retained most of the ICASA definitions and lookup structures, but introduced several extensions and modifications: (i) redundant model-specific parameters were removed, (ii) additional descriptors were added to document management practices and long-term rotation histories, and (iii) field operations were separated from experimental operations to make the dataset easier to interpret and harmonize, allowing users to trace experimental factors and variables more transparently.

This approach ensured that the resulting schema was user-friendly and logbook-like, while also being structured in a way that facilitated conversion into a relational database. This adaptation responded to the need highlighted by Bünemann et al. [29] for tools that allow a comparison of conventional and innovative sustainable cropping systems while also providing a harmonized and flexible framework.

The overall workflow of the SoilCare data pipeline is shown in Figure 1. The diagram summarizes the main stages of field data collection in structured spreadsheets, through quality control and semi-automated import, to storage in the PostgreSQL database and subsequent analysis and reporting.

2.2. Data Model

The first step was to translate the research methodology into a data model. All information was grouped into different pools: basic information (institution, persons metadata), field details (climate, inherent soil properties, spatial arrangement), experimental setup, management data, and measured data-results. The detailed analysis, the attributes of each table, and the development of the data model are described in Panagea et al. [30].

The different tables as well as the relations among these are shown in Figure 2. All the different types of relations that occurred between the different elements of an agricultural system were defined or definition was attempted. In the text, the table names are written in capitals and bold italics. The details about the attributes of each table of each pool, the required data types and formats, and the determination of the primary and foreign keys (absolute minimum data required to create a new tuple/element in the table), as well as the additional attributes which cannot be empty (no null), are included in the Supplementary Materials in File S1 Tables S1–S5.

2.2.1. Basic Information

In the first information pool, the basic information which defines an agricultural experiment is included. The EXPERIMENT table contains attributes such as the start and end date of the experiment, the statistical experimental design, and the objectives, as well as the data distribution details. Each experiment is linked to the institute which runs it (INSTITUTION), and the people that are involved (EXPERIMENTPERSON). In these entities, attributes which promote data provenance, such as the role and contact information of each person within the experiment, are included. Within each experiment, existing documents related to it (DOCUMENT), picture metadata (PHOTO), and a log table to include important events that may take place and that cannot be captured in any other element (DIARY) are also included.

2.2.2. Field Information

As the aim of this database was to store information about field experiments and cropping systems, it is inevitable that it will include information about these physical entities and the conditions on these. Each experiment can be carried out in one or multiple fields, for which information is contained in table EXPFIELD. Each field is characterized by its previous history, landscape (topography, shape, etc.), inherent soil properties (soil profile characterization, etc.), and climate (historical weather). Each field is thus linked with at least one weather station (FIELDWATHERSTATION) and its metadata (WEATHERSTATION_METADATA) for ensuring data provenance, as well as the daily records of the weather variables (WEATHER_DAILY). Also, each field is linked to at least one soil profile (FIELDSOILPROFILE) with its details (SOILHORIZON). As mentioned, experiments with different treatments can be carried out in several fields. In the TREATMENTFIELD table, the link of each field to the treatments that are carried out in this and the details of every experimental plot that is located in the field are captured (PLOT). In the table plot, the attributes allow the inclusion of both simple experimental designs (e.g., randomized) and more complicated ones (e.g., strip-split plot, Latin square), as several experimental units are included (blocks/row, main plot, column) to define the explicit design.

2.2.3. Experimental Setup

As most agricultural experiments are factorial experiments, each experimental factor, and its levels and description, are included in the table FACTORS, which is linked to the table TREATMENTS through the resolution table FACTORTREATMENT. In this way, the level of the factor or the combination of factors that characterize a treatment can be easily distinguished. The different attributes of this table include the replication number, description and further information, as well as an indication of which treatment is considered control/standard treatment or novel. The TREATMENTS table is linked to the field information pool for identifying the experimental plots where each treatment is allocated.

2.2.4. Management Data

Possible agricultural management categories identified were included, together with their events and details, in the management data pool. The categories included are PLANTING (crop rotations, cover crops, planting and harvesting of crops, etc.), FERTILIZER (mineral fertilizers, etc.), ORGANICMATERIAL (application of various organic materials, residues, etc.), TILLAGE (soil cultivation practices), CHEMICAL (application of pesticides herbicides, etc.), MULCH (materials applied in the soil surface), and IRRIGATION, which are linked to their relative events tables: PLANTINGEVENT, FERTILIZEREVENT, ORGANICMATERIALEVENT, TILLAGEEVENT, CHEMICALEVENT, MULCHEVENT, and IRRIGATIONEVENT. In each management category table, relevant attributes were included to provide information about the equipment used and the specifications of the applied materials (e.g., chemical composition, quantity, quality, etc.), and there was room for other important notes.

It is important to highlight that in these tables, there is a clear separation of the management events which are related to the experimental factors and the events which are related to the standard farm operations that occur in the field, using different coding formats in the relevant identities. In this way, it is clear and easier during the analysis to distinguish and query the main factors and the confounding variables that can influence the results of the experiments and possible relations. For example, take an experiment where each treatment receives different levels of organic materials and the experimental field is also tilled every year as a standard farm operation. In the table ORGANICMATERIAL, the attribute factor_level_ID should be defined with different codes for each level, while in the table TILLAGE, the attribute operation_ID should be determined for at least one tillage operation.

It is possible for field operations to take place on different levels and with different units. They can occur in the whole field, a treatment, or a specific plot according to the needs (e.g., one plot may require treatment against a local infestation). The users in the database should always define the lowest level at which the operation takes place (e.g., if the operation occurs at a field level, then the treatment and plot name attributes of the table should remain empty, and it is implied that all treatments and all plots receive the same operation).

2.2.5. Measured Data/Results

During the period in which an experiment runs, different variables are measured, either per field, per treatment, or most commonly per experimental plot/unit. These include variables about the soil, the crops, the soil water, nutrients, etc. The table OBSERVATIONMETADATA provides a level of data provenance with details about the variables, and the methods/apparatuses used to measure/calculate/observe each variable are included, as well as the units used in the documented variables and additional notes if required. The table OBSERVATIONS includes the values of each variable. Each record includes information about the sampling depth and sampling date and is linked to an experimental plot, treatment, or experimental field, again following the lowest level determination as for the management operations. Whenever one of the previous attributes changes, a new record is included in the table.

3. Results

3.1. Database Content and Structure

The database schema was developed and tested within the SoilCare project, which aimed to evaluate soil-improving cropping systems across 16 study sites in different European countries. In total, twenty-nine experiments were conducted, encompassing 142 treatments and 508 experimental plots. The experiments were carried out between 2017 and 2020, covering two to three growing seasons per site. Treatments were selected based on a literature review and consultations with local stakeholders. A shared methodology was developed to monitor experiments consistently across all sites but allowing relevance relevant to each location and objectives. The schema accommodates a wide variety of attributes. Static attributes, such as soil profiles, plot geometry, topography, and experimental design, were recorded once per experiment. Management events, including tillage, fertilization, sowing, irrigation, pest control, and harvesting, were documented with precise details on timing, equipment, and input quantities. Dynamic observations, such as crop growth and soil measurements, were captured repeatedly during each growing season. Additionally, meteorological data were integrated to allow cross-country and multi-season comparisons. The schema also accommodates different experimental designs, including randomized complete block designs and split-plot layouts, reflecting the diversity of field trials conducted across Europe. Overall, the dataset is extensive and complex, and the relational structure of the schema ensures that the data can be stored, accessed, and analyzed consistently.

3.2. Data Entry and Quality Control

Given the diversity of records and the relatively small number of users, fully automated data entry tools were not practical. Structured spreadsheets were adopted as a flexible solution that balanced ease of use with initial quality control. Each study site received a dedicated spreadsheet encompassing static information as well as annual updates for management events and dynamic observations. Spreadsheet protections, such as restricted cell entry and predefined formats, enabled study sites to perform a first level of quality control locally.

Subsequently, spreadsheets were submitted to database administrators, who performed additional checks based on expert review. Data were then automatically inserted into the PostgreSQL database using Python 3.6 scripts, which included automated checks for missing values, format errors, and internal consistency. In the early stages of the project, many spreadsheets required corrections, particularly regarding minimum data requirements and identifier coding. Over time, however, the submissions became more accurate, with most spreadsheets requiring minimal revisions by the second and third years. The spreadsheets also served as organized archives of results for each study site, supporting both record-keeping and reporting.

3.3. Data Access and Analysis

The relational database is hosted on a PostgreSQL server which, during the duration of the SoilCare project, was connected to an interface web application where users could access the system, browse queries, and download the data. The database with the monitoring data for SoilCare is also available in Zenodo in .sql and .tar file formats [31].

The empty database schema (SQL file), spreadsheet templates, and a detailed guide explaining input requirements have been made publicly available on Zenodo [30]. The spreadsheet templates are also included as Supplementary Materials (Files S2 and S3), together with instructions on how the template should be filled out and structured and example of how combined data can be extracted to answer specific scientific questions.

Centralizing the data storage provided multiple advantages. It enabled coordinated access across the consortium, allowing partners to efficiently retrieve data, conduct analyses, produce joint publications [32,33,34,35], generate factsheets [7] and plan future experiments using standardized and reliable data. Missing or inconsistent information could be requested quickly after quality control of the database. Standardized R scripts were developed and applied uniformly across all experiments. This enabled a consistent and streamlined statistical analysis process. Specifically, R scripts were created to perform mixed model analyses on the experimental data. Because all datasets could be exported from the database in the same structure and format, the statistical workflow became semi-automated.

Results were then merged with metadata into standardized Word templates, which were returned to study sites for interpretation and completion. In this way, a comprehensive report [35] and factsheets for each experiment [7] were produced for all the experiments within the SoilCare project. This allowed a timely and uniform report as a basis for interpretation and inferences across experiments. Importantly, the study sites remained responsible for the interpretation of the experiments they executed. The relational structure of the database also supports fully automated report generation, allowing experiment-specific data to be programmatically inserted into reporting templates.

3.4. System Application and Effectiveness

The database schema includes thirty-five entities, reflecting the diversity of cropping system experiments while maintaining usability. To balance detail with practicality, predefined value lists, such as for crop names or equipment types, were used instead of complex coding systems. This approach ensured that the database remained practical for everyday use while preserving flexibility for user-defined inputs.

The centralized system improved collaboration within the SoilCare consortium by enabling standardized reporting, factsheet production, and cross-site analyses. Its flexible and inclusive design makes it suitable for research institutes, international consortia, and farmer cooperatives that manage and analyze field experiment data. It enables efficient storage, retrieval, and sharing of information related to cropping systems, while maintaining standardized quality and structure. The structured design of this template later inspired similar efforts in other research initiatives, including the EJP SOIL Artemis project, which adopted and adapted its approach for developing a template for the on-farm monitoring of soil related ecosystem services and soil health [36].

4. Conclusions

Within the SoilCare project, a common database schema was developed to store all monitoring data in a consistent and structured way. This addressed common problems such as inconsistent data formats, missing metadata, and difficulties in comparing or interpreting results across experiments. The database also ensured proper access and management of information for all partners.

While the database is relatively small in size—storing data from 29 experiments—it is complex in terms of structure and content. This made it too large and detailed to manage effectively using spreadsheets alone. At the same time, the limited number of users (16 study sites) meant that highly automated solutions were not practical. Therefore, tailored solutions were developed for data entry, validation, and querying to meet the specific needs of the project.

The resulting database schema is a valuable tool for monitoring and documenting agricultural experiments or cropping systems. It allows for consistent storage of all relevant data under semi-strict rules that ensure quality without limiting flexibility. This supports both short- and long-term experiments and facilitates collaboration and information sharing between researchers and stakeholders. Most importantly, it helps ensure that valuable data is not lost over time or scattered across different sources.