The International Incident Database Project
by Rick Curtis
Risk Managers understand
the need to track close calls and accidents in their programs. Only by identifying
what is actually occurring in the field can managers respond to situations and improve program
protocols, staff training, etc. With this need in mind the International Incident
Database Project was developed.
The current state of our
industry is incredibly fragmented in this regard. Some programs keep no incident records,
others keep records on paper, some in spreadsheets, others in databases. The lack of consistency
across data collection means that it is currently impossible to compare types and
rates of incidents in any meaningful way. How can we effectively talk about something,
if we don't have a common definition/language? The goals for the project therefore
are to define a database structure that can serve as an accepted standard for the
field.
Why International?
Collecting and analyzing incident data is important for all outdoor programs. Although
the locations may be different, the activities themselves such as rock climbing,
ropes courses, and kayaking are much the same therefore the incidents that occur
are usually similar. Bringing together the best ideas from around the world means
that
we can create the most effective solution. In addition:
Insurance companies are global entities, developing an international database will
help demonstrate that the risk levels in outdoor education are much less than typical
competitive sports like soccer and should encourage companies both to provide insurance
and to reduce their rates.A number of outdoor education programs operate internationally so an international
standard will make it easier to integrate program data from around the world.What this will mean for Outdoor
Programs
Building a common database
format, using up-to-date database technologies will provide programs with a powerful
tool for recording and analyzing their own in-house
data. At the same time it means
that measurements generated by one program can actually
be compared against another in a meaningful way. It also creates the possibility of much more extensive research being
performed into the causes and responses to incidents, something which would benefit
the entire outdoor education industry.
This is the best time to embark on this
project. Currently there is no one database design standard in use in any
country. While there are databases in use within some organizations there is
no universal standardization. If we act now, we can develop together a standard
that can be put to use in countries around the world. If we fail to
take this step now, dozens of database and non-database
solutions will be developed leading to
multiple incompatible standards. Here are the proposed stages of development:
- Stage 1 - Gather a representative group of experienced
risk managers from around the world to discuss and design a standard database
structure for storing outdoor incident data.
- Stage 2 - Publish the database standard so that programs
can utilize it to catalog their incident data.
IIDB Committee
In order to move the process forward, I propose the creation of an International
Incident Database Committee with representation from programs around the world to
discuss and develop the common database standard four the outdoor education industry.
If you are interested in joining in on the discussion, email Rick Curtis (staff@outdoored.com)
to join in on our online conference discussion.
Future Enhancements
Establishing a common database format now allows for the possibility that in the
future data from numerous programs could be collected centrally in order to do more extensive statistical data analysis. Having an accepted database standard will allow
for the collation of data from various sources. The more data collected, the better
our ability to analyze it, identify trends, and make concrete recommendations for
program improvement.
Following the development of the database model future discussions could focus on creating a central storehouse where data is submitted allowing for research. There are of course a number issues that will need
to be resolved to collect the data centrally into an international incident
database, but the presence of those issues does not all diminish the importance of creating
a database standard now. Trying to combine data amongst dozens if
not hundreds of individual solutions will be exceedingly difficult. There are several
steps that would need to be taken to
Step 1 - Explore the idea of having a central database where incident information is submitted.
Step 2 - Develop a Web-based submission process so that
data can be easily submitted and analyzed.
Step 3 - Publish the results of the database analyses.
Database Design
Due to the nature of the information being tracked the database will need to
be a fully-relational database. The structure of the database will need to keep
in mind both the idea of a centralized data store that would be handled by a
larger client/server database like SQL Server or Oracle and desktop databases
like Microsoft Access of FilemakerPro that exist at the individual program level.
So the actual database design should take into account the ability to operate
on both of these levels.
If the project moves to a centralized Web-based application for data submission
to would require a high level SQL compliant relational database. Lower end desktop
databases such as Microsoft Access or FilemakerPro are not robust enough for
a large Web-based application and they don't provide the types of security functions
necessary to secure the data.
The initial database would be developed as a series of interrelated tables.
We expect that in 2-3 years the data would be moved to an XML-compliant database
(eXtended Markup Language). XML will become the lingua franca of data exchange
over the next few year (see below for more details).
The Tables
Some of the explanation that follows is aimed at those who do not have experience
in designing databases. Experienced database developers should skip to the
table definitions. You should understand basic database design--fields, primary
keys, foreign keys.
In this beta project all of the tables where given a prefix of wrmc since
the database was built on the current Wilderness Risk Managers Committee (WRMC)
incident report form. The prefix is good database design practice since it
is possible that a desktop version of the database might be developed in the
future and made available to organizations. If that database is incorporated
into a program's existing database structure, the table names might conflict
with already existing table names. A unique prefix eliminates this problem.
It is suggested that we select a new prefix (ex. idb for Incident Database).
The core table of the database is Incident. This tracks the bulk of information
submitting about a particular incident. Each field holds a single piece of
information about the accident. This is standard database design practice referred
to as Normalization. However, in some cases there may be multiple pieces of
information to record that is all considered to fall under one category. For
example, under injuries there might be multiple parts of the body that were
injured. There are several ways to deal with this.
| Solution |
Pros |
Cons |
| Add one field for each item |
Keeps all the data in one table. |
Makes the table extremely large. Adding a new piece of
information to be stored means adding an entirely new field. If there
are multiple copies of the database in use synchronization becomes extremely
difficult. Many database fields will be empty. Poor database design practice.
Less efficient database storage. |
| Allow multiple pieces of data to be entered in each field. |
Keeps all the data in one table. |
Extremely poor database design practice. Makes querying
the database for incidents that meet certain criteria extremely difficult
and time consuming. |
| Create Child Tables |
Best practice for database design. Allows for easily
adding new types of information. Does not create a "bulky" core
table. More efficient for querying the database for incidents that meet
certain criteria. |
Spreads data out over multiple tables which means that
greater database expertise is needed to develop and work with the data. |
The recommendation is to create a Parent table to handle the information that
is discrete (that is can only have one value such as the date of the incident)
and create a series of Child tables that store information where there may be
multiple pieces of data. This makes the database much more extendable. If new
information needs to be collected, either a new field would be added to the
Parent table or a new Child table could be created.
Database Structure 1.0
Since we are starting from almost scratch we can start with 1.0 as our
nomenclature. Below is a first iteration of the types of tables to be used in
a relational database for this project. This is based on the Wilderness Risk
Management Committee Incident Reporting database. The diagram shows the tables
and the various Parent/Child relationship. For those interested in the details of the Database Tables see Database Design Structure.
Web Access & Programming
Web Application: In order to expedite data entry from the largest number of programs the database would be served by a fully-programmed Web application. There are a variety of Web-based programming languages that are suitable to the task including Active Server Pages (ASP), ASP.NET, Cold Fusion, and PHP. I recommend using ASP or ASP.NET. Both provide good database connectivity and there are many third party components available to provide a richer user interface. Things such as customizable grids to display data, charting components, and reporting components are the major features that the Web site should provide.
Data Entry: The first level of the Web application is the data entry form. The form must provide several key features:
- Easy data entry: a well-developed data entry form that provides easy data entry.
- Data validation: through the use of client-side and server-side validation code data that is entered by users is verified to be in the correct format to be stored in the database (example, dates and times must be submitted in certain formats).
Data Viewing: The value of the data is for users to be able to view generic data on incidents which show basic type of incident, time of day, injury, etc. This presentation is similar to the written reports that have been issued on a biannual basis by the WRMC and other publications like Accidents in North American Mountaineering. The educational and risk management benefits of programs being able to view incidents cannot be underestimated. Data viewing would allow searches through a search form where a variety of search filters could be applied. Grid-style data views will allow viewing sets of data. Additional drill-down capabilities from the Grid display will show additional data in individual incidents.
Data Reporting: Additional value will come from being able to generate reports on the data. I propose that programs that submit data be given special access to advanced data reporting tools that will allow comparison reporting showing their data and generic data from other programs. Reports in both tabular form and graphs will show a particular program how they compare against other programs based on the search criteria which they would specify.
Costs
Development Costs: There are several stages to development of this project. OutdoorEd.com has done the primary database development and built a demo Web Application for testing purposes. OutdoorEd.com is volunteering space on their SQL Server and Web site to host a test platform for evaluation and development. The first stage, creating an application for Web-based data entry will require funds to pay for programming. The second stage, building data viewing and reporting capabilities will require additional programming.
Future Costs: If the project moves forward to developing a Web-based application for the central collection of data there would need to be a hosted Web Site with a client/server database. This most likely would mean paying a monthly fee to a Web hosting provider for a Web Site with a SQL compliant database. Cost would vary from $50 - $150 US/month depending on the size of the site and database. There would likely be costs for regular maintenance of the database and Web site. Additional costs should be anticipated and explored.
How Can I be Involved?
If you are currently managing your own incident database and/or have database expertise you can join in on review and discussions of the project through our online discussion
forum.
The Future of Incident Data Storage - XML
The long term future of the database rests with XML. XML is due to become
the lingua franca of data exchange. XML is similar to HTML in that it uses
a series of tags to enclose objects. HTML uses the opening and closing h3 header
tag to define a particular piece of text as a formatted header. This HTML tag markup
<h2>This is Heading 2 </h2>
results in this text displaying on the page with a Heading 2 format.
This is Heading 2
XML uses a similar set of starting and ending tags to define a particular set
of data. The beauty of XML is that it is self-describing. If an XML document
is sent across the Web someone on the other end can parse the information out
of the file. XML files are basic ASCII text files (just like HTML) that include
the appropriate tags to define the data. The current standard is to define an
XML Schema, which is a document the explains what each tag means. Publishing
the schema allows others to build incident database storage systems that comply
with the international standard and would allow for easy data exchange around
the world. For a primer on XML see http://www.javaworld.com/javaworld/jw-04-1999/jw-04-xmlp.html
Here is a sample of tagged XML data showing a cooking recipe. You can see how
a flat XML file can handle a whole range of hierarchical data similar to the
types of data collected on an incident.
<?xml version="1.0"?>
<Recipe>
<Name>Lime Jello Marshmallow Cottage Cheese Surprise</Name>
<Description> My grandma's favorite (may she rest in peace). </Description>
<Ingredients>
<Ingredient>
<Qty unit="box">1</Qty>
<Item>lime gelatin</Item>
</Ingredient>
<Ingredient>
<Qty unit="g">500</Qty>
<Item>multicolored tiny marshmallows</Item>
</Ingredient>
<Ingredient>
<Qty unit="ml">500</Qty>
<Item>Cottage cheese</Item>
</Ingredient>
<Ingredient>
<Qty unit="dash"/>
<Item optional="1">Tabasco sauce</Item>
</Ingredient>
</Ingredients>
<Instructions>
<Step>Prepare
lime gelatin according to package instructions </Step>
<!-- And so
on... -->
</Instructions>
</Recipe>