Schema Mapping for Conversions

:open_book: This guide explains the purpose of a schema mapping in the conversion process and details the steps for creating a mapping.

What is a schema Mapping?

The Specify database schema defines the tables, fields, relationships, and constraints that make up the database, as well as the rules and procedures for data entry, storage, retrieval, and manipulation. Not all components are customizable, but field and table labels, visibility, field formats, and controlled vocabularies (pick lists) can be!

A schema mapping is a spreadsheet that outlines how an external data source’s schema or structure will transfer to Specify 7. Rather than containing actual data, the mapping breaks down the requirements of your data and matches your existing fields and tables to fields and tables in Specify. When complete, the schema mapping spreadsheet will indicate exactly where external fields and tables belong in the Specify data model to guide the database customization and upload processes.

For the mapping spreadsheet, we recommend using a file format such as .xlsx and an application such as Excel or Google sheets, which allow spreadsheets to be saved with multiple tabs and customizations.

The Data Carpentry for Ecologists lessons offered by Carpentries are another fantastic resource. There, you can learn best practices for data organization in spreadsheets, data cleaning with OpenRefine, and data analysis and visualization via Python and R, and data management with SQL.

Why do I need a mapping?

First, a schema mapping guides the database customization process. The mapping indicates the constraints of each field, such as the type of data that can be entered, whether you require the field to be populated, or the controlled vocabulary the field must use. After you complete the Schema Mapping, you will refer back to it when you customize your database schema using the Schema Config tool in Specify.

After customizing your Specify Database, you will use the schema mapping to plan the upload process. You will refer back to the schema mapping when you create a WorkBench mapping, which indicates which table and field each column in your data spreadsheet will be uploaded to.

After you upload your data, the schema mapping will also serve as a record of exactly where your data is in Specify.

Step 1: Create your schema mapping structure

To begin your schema mapping, create a spreadsheet with the following pages. If your conversion involves data from multiple collections with different fields, it is best to create a separate schema mapping spreadsheet for each collection.

Field Information: This page will list every field involved in the conversion and match it to fields and tables in the Specify schema.

Note: If your data from a single collection has been exported into multiple spreadsheets with different fields, you may make a separate page in your mapping to represent the fields in each data spreadsheet.

Relationships (Optional): This page details how tables in your previous data source relate to each other. Columns to include: External Table Name, Relationship Type, Dependence, Specify Table Name, Foreign Key, and Primary Key.

Download our schema mapping template

This template includes the pages and columns necessary to complete Step 1 and Step 2 of the mapping process:

Schema Mapping Template.xlsx (93.2 KB)

Note: The template includes example information in the first and second rows, notes explaining the purpose of each column, and the supplemental pages Field Type Explanation and Relationship Type Explanation to reference as you fill out the mapping.

Step 2: Evaluate your data

The first step in the mapping process is to evaluate the data you plan to migrate to Specify. The objective of this step is to collect detailed information about what kind of data each of your fields and tables contain.

The Specify data model is built with both customization and consistent data cleanliness in mind. Precisely documenting the attributes of each of your fields will allow you to customize the database schema to suit your ideal data management practices.

Required Columns

For best results, the spreadsheet should include the following columns.

External Field Name
The name of a field from the data source you will upload to Specify.

External Table Name
If applicable, the name of the external table to which the field belongs.

Field Type
Specify categorizes fields using a “type,” which determines the kind of data that can be stored in the field.

Refer to the table below to categorize each of your fields as one of the field types below.

Field Type Description Valid examples (comma-separated) Invalid examples (comma-separated)
Text String with any character Female, 12 oz, 70% EtOH, 25,000 N/A
Number Whole numbers and decimals 1, 10, 2.5, -30.99987, -19, 100000 75%, 80g, 70 mm, 2005.05.001, 100,000
Integer Only whole numbers 1, -19, 20000 2.5, 75%, 80g, 70 mm, 2005.05.001, 100,000
Boolean Yes, No, or Null Yes, No, null, True, False Y, N, T, F, 0, 1
Pick list* Controlled vocabulary; only items defined in the pick list N/A N/A
Date Full valid date; must conform to one predefined format 7/7/2026, 1987-04-16 7/32/2026, 1987-04, Summer of 2023
Precision date Full valid date OR year OR month + year 7/7/2026, 2003, 1987-04 7/32/2026, 1987-00-01

*In the Specify database schema, ‘pick list’ is not a true field type. Rather, a pick list is a record representing a controlled vocabulary that can be enforced on fields with the ‘text’ field type. We will arbitrarily use it in this column to indicate that a controlled vocabulary must eventually be listed in the ‘Pick List Items’ column. However, during the ‘Match your data to Specify fields’ step, fields that use pick lists should be matched to ‘text’ fields in Specify.

[!Note]
Carefully consider whether every value in an external field meets the requirements of the Specify field type you select. Any values that do not conform to the field type’s requirements cannot be imported.

Maximum Character Count
The number of characters in the field’s longest value.

Is Required
Whether you would like Specify to require users to populate the field when creating a record.

Format
If applicable, the field format. For example, ######### or YEAR-####.

Pick List Items
If a field uses a controlled vocabulary, create a comma-separated list of permitted values in this column.

Recommended Columns

The following columns provide additional context for each field. They should be included if people with less knowledge about your data management practices (eg. IT staff, Specify Support) will be assisting with the conversion process.

Description
A description of what the field records.

Example Value
An example value from your data.

Specify Table Name
If you have an idea of which table in Specify the field might fall under, indicate the table name here.

DarwinCore Term
If your field represents a DarwinCore term, indicate the term here.

Step 3: Analyze the Specify 7 data model

Once you have a breakdown of your data, you can begin learning about the Specify 7 data model and consider how your data fits.

The best way to explore the Specify 7 data model is by using the Database Schema Viewer, which can be accessed from within all Specify 7 databases. This page offers an organized interface where you can easily navigate between tables in Specify to view their fields and relationships. It displays information such as field types, requirements, relationships, descriptions, and custom captions for every field and table.

To see an example of the Schema Viewer, use our live demo database.

Additionally, see the list of table descriptions below:

Data Model Download

The data model can be downloaded from the Schema Viewer in TSV format and opened as a spreadsheet using applications like Excel, Google Sheets, or LibreOffice Calc. Viewing the data model in spreadsheet format allows you to use filtering and sorting features to filter out fields that should not be used in the mapping.

To help you narrow down the list of possible tables and fields for your mapping, we recommend filtering out rows with the following values:

  • Is System (Yes): The rows with Is System as Yes are primarily attachment and utility tables. You should not need to upload into any of these fields. If you need to upload attachments, you should do so after your conversion is complete using the Batch Attachment Uploader.
  • Read-only (Yes): The rows with Read-only as Yes are typically calculated fields or timestamps, which cannot be uploaded into.
  • Relationship (Yes): This denotes whether or not the row is defining a field or a relationship. When deciding which field to map to, you can filter out any rows that have Relationship as Yes to prevent mapping to a non-field.
    • However, before mapping fields, you should note down any relationships. Some relationships are particularly helpful, such as Collection Object and Collection Object Attribute. There is only one Collection Object Attribute per Collection Object, and its usage is to provide significantly more fields to upload into.

Step 4: Map your data

Once you are familiar with the Specify data model, you can begin matching the fields and tables from your data to fields and tables in Specify.

Map to Specify tables

Begin by matching tables or categories from your external data with tables in the Specify schema. Review the descriptions, fields, and relationships for each table that might apply to your collection. Pay attention to relationship types (one-to-one, one-to-many, many-to-one) and whether relationships are Independent or Dependent.

After comparing that information to tables from your external data, write the name of the best match in the ‘Specify Table Name’ column of your spreadsheet. For maximum clarity, we recommend recording the entire path from the “base table” of your Field Information page, including the relationships between the “base table” and your destination.

“Base table” refers to the table that is primarily represented by every row of a particular spreadsheet. If each row of your spreadsheet will create a Collection Object record, Collection Object will be the base table.

If your base table is Collection Object and you’d like to map to Specify’s Locality table, you would write the name of the base table (Collection Object), the destination (Locality), and any related tables between them (Collecting Event). A quick way to find this path is to create a query from your base table in Specify and navigate to the correct table.


In this case, you would write Collection Object → Collecting Event → Locality in the Specify Table column of your mapping spreadsheet.

Map to Specify Fields

For each field in your schema mapping, view the associated table you chose in the Data Model Viewer. Review the field attributes you recorded in Step 2 and compare them to the attributes and descriptions of each Specify field available in that table. In the ‘Specify Field Name’ column of your spreadsheet, write the name of the most appropriate Specify field.

By default, the Specify schema contains a number of fields that are typically used by biological collections. Just a few examples include:

  • Catalog number (Collection Object)
  • Remarks (Collection Object)
  • Type status (Determination)
  • Field number (Collection Object
  • Locality name (Locality)

Multiple supplementary fields with different field types are also available. These additional fields exist specifically for users to customize fields that are more specific to their collection and workflow. Customizable fields look like:

  • text1
  • number1
  • integer1
  • yesNo1

Now, each field should be mapped to a specify field and table/table path.

Specify fields that should not be mapped

This is a list of Specify 7 fields that should not be used when uploading to the Workbench and should not be mapped to in this phase.

  • ID: This is Specify 7’s ID for each record, and cannot be uploaded into or referenced, although it appears within the Workbench mapping options. Attempting to upload data to this field will result in an error. To import identifiers, you should use a different field, such as GUID.
  • Timestamp Modified: This field is entirely omitted from the Workbench, as uploading would change the Timestamp Modified to the current time regardless.
  • Version: This tracks the amount of modifications to a record within Specify 7, and should not be uploaded into.
  • Created By Agent: The Created By Agent will always be the uploader of the data set, overwriting any data uploaded to this field.
  • Modified By Agent: This field is blank by default and can be uploaded into. However, it may be overwritten later with any future edits to the record.
  • Timestamp Created: You can upload into this field, but since you cannot upload to Timestamp Modified, this may cause some confusion.

Tips for Mapping to Specify Fields

  • Pick lists/Formatted fields: Fields that will use a pick list or field format should be mapped to fields with the ‘text’ field type.

  • Consider character limits: Map fields with short values (pick lists, formatted, etc) to Specify fields that have smaller character limits. This reserves the fields with larger character limits for data with longer values.

  • Avoid using unwarranted tables: While many tables in Specify are designed to fulfill a certain purpose, you do not always need to use those tables just because some of your data serves the same purpose. For example, you may choose to add a Sample ID field to the Preparation table instead of the Material Sample table if you do not track any other sampling data in Specify.

  • Consider data cleanup: Set your database up for success by choosing fields that will enforce data consistency going forward. If one of your fields records dates but some values are invalid, consider cleaning the data rather than using a text field.

  • Find room for your data: Specify 7’s schema is fairly customizable, allowing you to change field and table names from within the Schema Config. If your data uses fields or tables without an exact match in Specify, it is fine to assign them to a user-definable field or unused table so long as it meets the necessary data constraint requirements (eg. field types, relationships, character limit).