Sampling at a nest box site

This example outlines a possible database structure and data entry workflow for monitoring nesting activity at a (nestbox breeder) bird colony. Our example provides a useful framework, for instance, if we wish to track changes in breeding success at a nesting site over several years, or if we are conducting behavioural research and wish to measure the behavioural responses or breeding success of selected individuals in comparison with other individuals, as well as across different years and under different management treatments.

It demonstrates how static data, time-dependent information and observations can be stored in interlinked tables, rather than attempting to manage them in a single, very wide table or in spreadsheet programmes.

For general guidance on planning a data collection and representing its entities and relationships, see Data collection. For an explanation of the distinction between observation events and occasional observations, see Observation events and occasional observations.

Defining the main entities

To work at a nest box site, we need a database structure suitable for recording both the nest boxes themselves and the breeding activity that takes place within them. If maintenance work is carried out on the nest boxes, the structure must also be able to represent it.

Different types of information should therefore be stored in separate tables. We will have one table for the nest boxes, another for their time-dependent status and location, a third for breeding events, and a fourth for brood-management or maintenance activities. These tables are linked to one another, but it is not recommended that they be managed in a single, very wide table. Instead, they should be organised into several smaller related tables.

Creating the nest box register

Our first task is to create a nest box register. We need to track the information recorded for each nest box, including how the box is identified in the field, when it was installed, and whether it is still present or has been removed.

If the same field identification number might accidentally be assigned to two nest boxes, we should also introduce an internal identifier that is guaranteed to be unique for each nest box. It would be preferable to ensure that a field identifier, such as number 102, does not appear on two separate nest boxes, but this may be difficult to guarantee in a large nest box colony.

Alternatively, an additional identifier could be attached to each nest box and designed to be readable only at close range. For example, it could be a number engraved on an individually manufactured metal plate, ensuring that no two plates are the same. If such a system is used, both the field identifier and the unique identifier should be stored in the nest box register.

Stable identifiers should not be inferred solely from labels, coordinates, or other values that may change. For more information about identifiers and relationships between tables, see Tables and relationships.

Separating stable and time-dependent information

A nest box may not always be active or even present at the site. It may be removed for refurbishment and left out for an entire season. Its status therefore cannot always be represented by a single Boolean value. Instead, the status must be associated with a date or time interval, and more than one status record may be required to determine retrospectively when the nest box was active and when it was not.

For this reason, our nest box register consists of two related tables:

• a nest base table containing stable information about each physical nest box; and

• a nest register table containing time-dependent information about the box, such as installation, status, and location.

The nest base table does not need to store status information. It stores the nest box’s unique identifier and may also contain its date of manufacture and properties such as the material from which it is made.

A UNIQUE constraint must be applied to the unique nest box identifier. For information about PostgreSQL unique constraints, see Unique constraints in the PostgreSQL documentation.

The same unique nest box identifier also appears in the nest register table. It does not need to be unique in this table because one nest box can have several status records. However, the database must not allow a record to refer to a nest box that does not exist in the nest base table. This relationship is enforced with a foreign key. For more information, see Foreign key constraints in the PostgreSQL documentation.

The nest register table contains records of nest box installations and other information that changes over time. A new row may, for example, be added for each nest box every year. This table must contain at least one date field and will normally also contain a field indicating the nest box’s status. It is also important to include a spatial location field, such as an OpenBioMaps geometry column containing the recorded GPS location.

For guidance on creating project tables, registering columns, and assigning semantic roles, see Database tables and columns. For general spatial data guidance, see the Location and geometry section of the data-collection documentation.

If the colony contains 200 nest boxes, the size of the nest base table will remain unchanged over the years, provided that no new physical boxes are added. The nest register table, however, may grow by 200 rows per year or even more. This growth is expected and is not a problem. The table contains the history of the colony, and querying the most recent applicable record for each unique identifier provides its current status.

Recording breeding events

The next table is the breeding events table. It functions similarly to an event log and contains a foreign key linking each breeding event to the unique identifier of the relevant nest box. It must contain at least one observation date or date-time field, together with the additional fields required to describe and analyse the breeding event.

The fields must be defined according to how their values will be processed. Data intended to be treated as numbers must be stored in numeric database columns. For example, if the number of eggs is required for analysis, it should be stored in an integer field. Values such as approximately 8 eggs or eggs must not be accepted in that field.

If the database column is defined as an integer, data entry following fieldwork can accept only valid integer values. If a field note is unclear, the data collector or data-entry operator must make and document an appropriate decision while the necessary context is still available. Half a year or several years later, the ambiguity may no longer be resolvable, and the value may have to be omitted from an analysis.

For more information about quantities, units, explicit non-detections, and sampling effort, see Data collection.

Recording brood-management and maintenance activities

The fourth table records brood-management or maintenance activities. Its relationships may differ from those of the other tables. Some operations may relate directly to a physical nest box, while others may relate to a specific breeding event. The table will therefore probably need a foreign key to the nest base table and may also need a foreign key to the breeding events table.

This table is referred to here as the brood-management table.

Implementing the structure in OpenBioMaps

PostgreSQL constraints help guarantee consistency, which is essential for long-term data management in a complex project. OpenBioMaps provides the tools needed to configure and use the PostgreSQL structure through project interfaces.

The project tables and columns should be created and registered through the OpenBioMaps administration interface. For instructions and relevant warnings, see Database tables and columns.

Displaying active nest boxes on a field map

In the field, observers first need to locate the nest boxes. A map can be generated from the nest register table. OpenBioMaps can display this data after a query has been configured for the relevant table and connected to a map layer.

For an overview of SQL query templates and map configuration, see SQL query settings and Map settings.

The resulting map can be displayed by compatible OpenBioMaps clients. The OpenBioMaps progressive web application can be used for fieldwork, and supported mobile clients may also provide database-query and map-display functions. See Progressive web application and Mobile applications for current client capabilities.

Only currently active nest boxes should normally appear on the operational field map. One possible implementation is to create a PostgreSQL view that returns only the current active record for each nest box. This creates a virtual table containing the nest boxes that should be displayed. For example, the view could be named current_nest_boxes.

For information about managing views, see Managing views.

Creating the upload forms

At least four types of records must be entered into the four tables:

• stable nest box information in the nest base table;

• time-dependent nest box information in the nest register table;

• observations in the breeding events table; and

• interventions in the brood-management table.

We therefore need at least four upload forms. A project may create additional forms when different workflows, clients, user groups, or validation requirements apply to the same table.

For detailed instructions on configuring and publishing forms, see Upload form management.

Selecting the correct nest box

Recording a breeding event requires the observer to select the correct nest box. A painted field identifier may not uniquely identify a physical box, and it may also be mistyped. The form should therefore provide a list of currently active nest boxes from which the observer can select.

Ideally, the observer should be able to view the same nest boxes on a map to confirm the selection. The current_nest_boxes view described above can serve as the source of the list. The form can store the nest box’s stable unique identifier while displaying its more familiar painted field identifier.

The OpenBioMaps form editor supports list values obtained from database tables. See the List definition and joint-list sections of Upload form management.

Selecting a species

A breeding-event form will normally need a taxon or species-name field. The field should use a controlled and documented taxonomic list. For a short project-specific list, one species name can be entered per line in the form list editor, which converts the values to a JSON list used by the form.

For a larger or maintained taxonomic reference, an autocomplete source is usually more appropriate than a static list. See the taxon-information guidance in Data collection and the autocomplete and list-definition sections of Upload form management.

Recording coordinates, observers, and dates

It is useful to record the coordinates of each breeding event even when the nest box has an existing registered position. The independently recorded location may help identify an incorrectly selected nest box by comparing the event location with the registered location.

The geometry field may be hidden from the user or displayed as read-only, but display settings alone must not be treated as security or integrity controls. The submitted value must also be checked and handled correctly on the server.

An observer field can be populated automatically for the signed-in user. Additional observer fields may be required when several people work together. A multiple-selection list can be used when several additional observers must be selected in one field.

The observation date can also be populated automatically and made read-only. However, the observation time must remain distinct from the database insertion or upload time. Projects must also provide an explicit workflow for legitimate retrospective data entry.

For more information, see the observer, date and time, and location sections of Data collection. For form defaults and display options, see Default values and Field display options.

Using related fields

Separate fields should be created for observations that need to be queried or analysed independently. Relationships between these fields can then be used to control validation.

For example:

• Are there eggs in the nest? — obligatory Boolean field;

• Number of eggs — optional integer field that becomes obligatory when eggs are present;

• Are there chicks in the nest? — obligatory Boolean field; and

• Number of chicks — optional integer field that becomes obligatory when chicks are present.

OpenBioMaps can change field behaviour according to the value of another field. For example, if eggs are present, the number-of-eggs field can become obligatory. Client support for such relations may differ, so the behaviour must be tested in every intended interface and validation should also occur on the server.

For details of the relation syntax, see the column-relations section of Upload form management.

Using dependent lists

Supported clients can organise selectable options according to a value chosen in a preceding list. For example, a form may first ask whether the nest is active:

• if the answer is yes, the following field may offer eggs or chicks; and

• if the answer is no, the following field may offer abandoned, predated, empty, other contents, or nestbox not found.

This can be implemented with a joint or dependent list. For configuration details and examples, see the joint-list section of Upload form management.

Testing the workflow

Before using the forms for production fieldwork, submit realistic test records through every intended client. Tests should include new and existing nest boxes, duplicated field labels, moved or temporarily removed boxes, empty nests, uncertain counts, multiple observers, unavailable GPS positions, retrospective entry, and interrupted or offline submissions.

Verify that the resulting records can be queried and joined without relying on undocumented assumptions. Also confirm that public, authenticated, and group-specific users receive the intended form and data access.

For a broader pre-publication checklist, see the practical checklist in Data collection. For form publication and versioning, see Upload form management, and for project permissions see Data access.

Sampling at fixed sampling sites along fixed transects