Enhancing Data Integrity: Journey North's Transition to a Relational Database

Growth of a Program

Journey North began at the dawn of the internet. Early efforts to track wildlife migrations were done the "old-fashioned" way, via phone, letters, and emails. Online data entry systems were in their infancy. Experts in the field provided detailed information to Journey North staff, who then displayed this information on static maps and shared updates with the public through emailed news stories. Many educators and students, the early audience of these migration stories, benefited greatly from hearing directly from experts.

As interest in these migration stories grew, technological advances allowed for greater public involvement. In the late 1990s, Journey North created a data entry portal and encouraged more people from across North America to contribute data on wildlife migration. In 1996, 270 observational reports were submitted. In 2023, volunteers submitted approximately 44,000 observational reports.

The Challenges of a Flat Database

Journey North originally used a flat database to collect data on migratory species, aiming to create an easy-to-use reporting system. Staff set up a flat data table with simple categories like Monarch Adult (FIRST Sighted). This system worked for 25 years, but problems arose over time. Different staff members used varying syntax to collect similar data, resulting in inconsistencies. For example, monarch observations were categorized under both Monarch Adult (FIRST Sighted) and Monarch Adult (First Sighted). Moreover, the limited categories often confused volunteers who wanted to report complex observations, such as multiple species of hummingbirds nectaring on early blooming flowers. Volunteers often included detailed text-based comments, making data analysis challenging.

The flat data table increasingly led to frustration, missed data, and errors.

Modernizing the Database

In 2021, Journey North staff decided to transition from a flat database to a relational database. A relational database organizes data into multiple related tables, keeping it accurate and well-organized. This structure facilitates complex queries, easy retrieval of information, and flexibility in adding or deleting fields in the future. Relational databases enhance data governance by ensuring data integrity, consistency, and security, making them more reliable and scalable as data volumes grow.

By transitioning to a relational database, Journey North can better manage its extensive observational data, reducing errors and maximizing its use for scientific research on migration and biodiversity.

Submitted June 2024