You Probably Need a Safety Database, Right?

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.

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;

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.

The table "ClassificationParameters," 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

• How to Choose the Best Aviation Safety Database Software
• Tips Writing SMS Manuals with Aviation Safety Database Solutions
• What Is an Aviation Safety Database for SMS Implementations?

Setup Table Relationships between Safety Data Types

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. You may probably 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 Classification 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 needed 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.

Last updated in November 2023.