SMS Pro Aviation Safety Software Blog 4 Airlines & Airports

How to Design an Aviation Safety Database for ICAO SMS Programs

Posted by Christopher Howell on Jun 1, 2022 12:19:24 PM Find me on:

You Probably Need a Safety Database, Right?

How to Design an Aviation Safety Database for ICAO SMS Programs

I'm guessing if your airline or airport has more than 20 employees you will need a database for many of your SMS requirements.

If you don't have the budget for a database, or if you are one of those talented safety managers with excellent IT skills, then you can build your own safety database.

In this article, we demonstrate how you can design your own safety database within an hour or two.

So let's get started.

Step #1 - Determine the Requirements for Your Safety Database

Not all operators have the same requirements. You may be storing much of your documentation in SharePoint or on the "network."

Question: What do you need from your aviation safety database?

Answer: I need to manage the requirements for an ICAO compliant SMS program.

OK, what are the requirements? Quick, read ICAO Document 9859 (and fall asleep and never get anything else done).

There is an easier way. Let's discover quickly what we need to store by reviewing the SMS Documentation that was condensed for your convenience.

Incident reports must be stored in the aviation safety database if you are operating under EASA jurisdiction

From the list on the SMS Documentation link, I see some documentation requirements that scream, I need to be in a database.

These are:

  • Incident reports;
  • Safety performance indicators;
  • Safety risk assessments; and
  • Investigations;

This would be a bare-bones database for your aviation SMS requirements. The other documentation in the list could be managed using Word files and Excel spreadsheets. As a matter of fact, if you are an operator under EASA jurisdiction, your company is required to store safety occurrence reports in a database per EASA Regulation No 376/2014.

Step #2 - Create Tables and Columns to Hold Aviation Safety Data

From the above analysis, I can see at least four tables: I would name them:

  • IncidentReports;
  • ClassificationParameters (to hold Safety performance indicators);
  • RiskAssessments; and
  • Investigations;
Risk management processes will follow certain procedures that must be documented in an aviation safety database or Word files.

For each of these tables, create a column for each specific data element you wish to capture. For example, in IncidentReports, you may have:

  • ReportId (primary key);
  • DateReported;
  • ReportedBy;
  • Location;
  • Title;
  • ShortDescription;
  • TypeOfReport;
  • Witness1;
  • Witness2; etc.

A good rule of thumb is that for every distinct element on your hazard reporting forms, you will have a column to hold the data.

For each of these tables, you should have a primary key. Usually, this is an incrementing integer or GUID. I prefer integers for primary keys as they are more usable.

For the table "ClassificationParameters," this will be used to hold all unique Classifications you wish to use in your reports. These classifications will also contain your key performance indicators, also known as safety performance indicators. This table will have these columns:

  • ClassificationId (primary key);
  • ClassificationParentId (foreign key for hierarchy)
  • ClassificationName;
  • ClassificationDescription;
  • ClassificationType; and
  • IsKeyPerformanceIndicator.

Remember, we are creating a very simple aviation safety database and there could be potentially other columns. We add other fields in this table, such as:

  • Trending Threshold Alert Levels;
  • Financial Tend Alert Level;
  • Requires Investigation; and
  • ADREP Mapping fields.

Related Aviation SMS Database Articles

Setup Table Relationships between Safety Data Types

Aviation risk management software is important for safety operations today. Most aviation service providers need a safety database.

Your RiskAssessments and Investigations tables will have a relationship and a foreign key from IncidentReports table.

Your RiskAssessments table may have these columns:

  • AssessmentId (primary key);
  • ReportId (foreign key);
  • AssessmentStage (initial, closing, review);
  • Probability;
  • Severity; and
  • DateAssessed.

The Investigations table may have these columns:

  • InvestigationId (primary key);
  • ReportId (foreign key);
  • Date;
  • Contributing factors;
  • SequenceOfEvents; etc

Again, your table columns will mirror your investigation reports. It is probable that you may have up to six supporting tables just to manage investigations. We do this for data normalization, which keeps us from having redundant data. You can learn more at the offered link.

While our quick safety database now has four tables, this is very simple. Our aviation safety database has over 400 tables, but we've been doing this for many years.

Many to Many Relationship between Incident Reports and Classifications

We have an IncidentReports table holding all the reported issues. We also have a Classifications table that holds a list of all possible classification parameters. We need to create a many-to-many table relationship to allow reported issues to be classified. As you know, a reported issue may be classified in many ways.

For example, we may have a runway incursion caused by faulty equipment and poor runway conditions. Both of these items are valid classifications. We need a many-to-many relationship table to hold this data. The table could look like this:

Table Name: ReportClassifications

  • ReportId (primary key);
  • ClassificationId (primary key);
  • DateClassified.

Final Thoughts on Designing an Aviation Safety Database

This is a very simple safety database.

We defined the requirements for our database. We identified which data we need to store in the safety database.

Then we started creating tables.

Don't worry if you have to go back and make changes. This is a normal process. We have had tables that were designed ten years ago and they occasionally have more data columns added as our needs change.

You will now need a user interface to add the data to your safety database. The user interface can be Web forms or MS Access forms.

We never settled on a database type yet, but we prefer Microsoft SQL Server Database or Oracle Database for enterprise-grade safety solutions.


List of Aviation Safety Classifications Based on Hazards

You probably would like to have some classification parameters to add to your Classifications table. Here is a list of over 200 hazards across multiple categories, including:

  • Air Operations;
  • Airport;
  • Human;
  • Environmental;
  • Maintenance; and
  • Organizational.

Download Free Aviation Hazard Classifications List

Published December 2015. Last updated in June 2022.

Topics: 2-Safety Risk Management

Site content provided by Northwest Data Solutions is meant for informational purposes only. Opinions presented here are not provided by any civil aviation authority or standards body.

 

 

Benefits of SMS Pro Database

Affordable, Basic Compliance for Small Aviation Service Providers

Best Practices for Aviation SMS

 

Watch SMS Pro Demo Videos

These two on-demand videos offer:

  • High-level overview of SMS Pro;
  • Hazard Reporting & Risk Management walk-through.
Watch SMS Pro Demo Videos
 
 
 
 

Subscribe to Email Updates

Recent Posts