911/112: An Emergency Call Service Data Model

Calling an emergency number like 911 or 112 is not something we’re looking forward to, but we’re glad to have it when we need it! On the other end of the line, it’s a stressful job, and there’s little room for mistakes. Everything needs to work perfectly.

Today, we’ll take a look at the data model an emergency service could use to process and respond to incoming calls.

Idea

Emergency numbers differ from country to country. The numbers 911 (North America and some other countries) and 112 (Europe and parts of Africa and Asia) are widely used. These numbers are used to contact all three emergency services (police, ambulance, and fire and rescue) in one call. Some countries use a different number; others don’t have a centralized emergency number. In this model, I’ll focus on situations where such a centralized number exists.

The main idea is that when someone makes a call, an operator takes care of that call, collects all relevant information, and forwards this information to those in charge. One example could a car accident: after receiving the call, the operator should know where that accident happened and how serious it is. They can then send the police and an ambulance to handle the situation. Another example could be a fire in an apartment building, which could require all three emergency services.

Data Model

The data model consists of three subject areas:

• Countries & cities
• Calls
• Actions & services

We’ll describe each of these subject areas in the order they’re listed.

Countries and Cities

This subject area is not specific to this model, but it is still needed to track the locations where calls came from.

We have only two tables in this subject area. The country table contains a list of UNIQUE country_name values. We can expect that we’ll have only one country here because emergency services mostly function on the national level. In a larger country, this table could be used to store state or province names.

A list of all cities and villages is stored in the city dictionary. For each city, we’ll store a UNIQUE combination of country_id - city_name. We can expect that this table will contain a list of all cities and villages in a certain country.

Calls

There are two subject areas which are specific to this data model: Calls and Actions & services. In the stream of time, calls come first and trigger other events. Therefore, we’ll describe this section first.

The Calls subject area is composed of five tables. While the call table is obviously the central one, we’ll describe the other four tables first because they all are referenced in the call table.

Users initiate calls using their landline or mobile phones. We need to store each number that called 112 or 911, so we’ll need a phone_number table. Each time a new call is initiated, we’ll check if the number already exists in this table. If not, we’ll insert a new row. For each table record, we’ll store:

• phone_number – The number that initiated a call.
• number_status_id – A reference to the number_status dictionary. This value shall denote if the number made the “first contact”, is “blacklisted” or “blocked”, etc. This value could help us in deciding what to do, e.g. not to create a new call if a number is blocked, throw out a warning if a number is blacklisted, or simply record info for the operator.
• notes – All notes related to that number, inserted by any operator. This is not a required field and will mostly contain NULL values.

The operator table is used to store a list of all operators that receive calls. For each operator, we’ll store a UNIQUE operator_code (an internal designation), the operator’s first_name, and their last_name. We won’t store details here, like operators’ contact information or a flag denoting if the operator is currently busy or not.

For each call, we want to assign a certain status. To do that, we’ll first need a call_status dictionary. This dictionary contains a set of UNIQUE status_name values. Some expected values are: “call interrupted”, “call dropped”, “successful call”, and “call rerouted”.

Now we’re ready to describe the call table. A call is initiated by the caller. After the number is inserted in the database (if it’s a previously unknown number), the call is inserted too. For each call, we’ll need to store:

• operator_id – A reference to the operator that received this call.
• phone_number_id – The number that made the call. In almost all cases, this attribute will contain a value referencing the phone_number table. Still, I left an option to insert a call without a phone number. This could happen when a number is hidden or if there is some kind of a network error.
• call_status_id – A reference to the call_status dictionary that describes the call outcome. This value will be inserted at the end of the call.
• city_id – A reference to the city dictionary, denoting the city where the call was made. This could also be NULL, as this info could be unknown or unneeded.
• call_start_time – Denotes when the call started. It can be NULL in some special cases, e.g. the operator heard the line ring, but the call was never actually established.
• call_end_time – When the call ended. This value will be updated at the actual time the call ends. It will contain a NULL value if the call never actually started, or if the call started but is still in progress.
• notes – All notes, in free textual format, that the operator inserted regarding this call.

Actions and Services

After a call is made, it’s time for action. These actions should automatically alert required emergency services; we should also be able to insert or remove alerts as needed.

To cover this, we’ll use five more tables.

In the emergency_service table, we’ll store a list of all available emergency services. This table contains a UNIQUE service_name and any information needed to establish a contact. Contact info is stored in a structured JSON-like format in the contact_details attribute. Some of the expected emergency services are “police”, “fire department”, and “ambulance”. Still, we could have others too, like “mountain rescue”, “civil guard”, etc.

The action_catalog dictionary contains a list of all possible actions that could be required as a result of a call. This table contains a list of such UNIQUE action_name values. Some expected values here are “alert all services”, “alert ambulance”, etc.

Now we need to define a list of all alerts that should automatically occur when an action is assigned to a call. These values are stored in the alert_service table. We’ll store the UNIQUE pair action_catalog_id – emergency_service_id, denoting that a certain emergency service should be contacted when this action is assigned. Still, sometimes we might want to revise this, so I’ll leave an option to do that. If the flag always_alert is set to True, we’ll send this alert without manual supervision; otherwise, the operator can intervene.

Assigning an action to a call is done via the action_required table. We may need to have more than one action for each call, so we need this table. We’ll store the UNIQUE combination call_id – action_id as well as the notes, if any, inserted by the operator.

The last table in our model is the alerted_service table. UNIQUE pairs of action_required_id – emergency_service_id denote the actual alerts that were initiated for that action (and call). These will be all records with the alert_service.always_alert set to True and all alerts manually set after the operator revised them.

Possible Improvements

This model is just the backbone of one possible solution. I can personally suggest many improvements:

• How operators’ data is stored.
• Including the possibility to track what happened after emergency services were alerted.
• Letting an operator initiate a call.
• Relating events in the database so we could define if a certain call was related to another call, action, or alert. At the moment, we only know their order.

How do such services work in your country? Did we miss something? What would you add or remove from this model? Please tell us in the comments below.