wiki:Domain/EmergencyManagement/IncidentManagementSystems/IncidentCommandSystem(UnitedStates)

Version 6 (modified by Nick Arnett, 11 years ago) ( diff )

Revised schema

Incident Command System

Required in the U.S. for disaster response. This is a bit of brainstorming on what is needed to fit into ICS for use by Citizen Corps programs (CERT, Neighborhood Watch, etc.) Initial version not quite done... still thinking about best way to organize people, roles, incidents, schedules... NDA

Because one of the principles of ICS is that it can be used for all sorts of "incidents," including training, exercises, etc., this model treats everything it possibly can as an incident, using incident types to distinguish. Part of the intent is to encourage teams to use ICS regularly.

Components

  • Location: address, coordinate, postal info
  • Person: individual
  • Role: hierarchy of structure and command
  • Organization: companies, teams, agencies, etc., with inter-relationships
  • Membership: relationship of people to organizations
  • Incident: deployment, training, exercise, debriefing, etc.
  • Credential: training, skill, certification, vaccinations, etc.
  • Prerequisite: what credentials needed before a person can participate in an incident
  • Event: what happened at a particular time and place; logging
  • Equipment: personal and organizational gear
  • Resource: supplier of equipment, skills, etc.
  • Project: workflow for planning and management
  • Email: contact info
  • Phone: contact info
  • Web page: associated with people, organizations, resources

Functions

  • Add, modify, remove each kind of component
  • Generate lists of each kind of component
  • Print identification cards/badges
  • Select individuals by various criteria, then send notifications
  • Interface to calendar, recurring automatic emails for meeting notifications
  • Issue training certificate
  • Alerting - instant messaging, SMS, printed phone tree generation
  • Export KML data for mapping, with symbols
  • Dashboards for incidents, teams, resources, projects
  • Training reports - who needs which courses, what expires soon?
  • ICS/NIMS forms
  • Access control
  • Automatic data dump to forms suitable for laptops, mobile - cache critical data for when network is down.
  • Data import
  • MORE TBD

Table structures

person

person_idINTEGER(8)UNSIGNEDNOT NULL
person_titleVARCHAR(40)
first_nameVARCHAR(40)NOT NULL
last_nameVARCHAR(40)NOT NULL
nicknameVARCHAR(40)
photoBLOB
physical_limitationsTEXT
commentsTEXT
genderENUM(m,f)
birthdateDATE
statusENUM('interested_volunteer,pending_volunteer,active_volunteer,inactive_volunteer,liason,contact')NOT NULL
emergency_contactINTEGER(8)UNSIGNED
credential_idINTEGER(8)UNSIGNED
phone_idINTEGER(8)UNSIGNED
PRIMARY KEY(person_id)
UNIQUE KEYIDX_person1(person_id)
KEYIDX_person2(person_id,credential_id)
KEYIDX_person3(person_id,phone_id)

role

role_idINTEGER(8)UNSIGNEDNOT NULL
ics_levelENUM('Command,Section,Division,Individual')
ics_roleENUM('PIO,Safety,Operations,Planning,Logistics,Intelligence,Finance/Admin')
PRIMARY KEY(role_id)
UNIQUE KEYIDX_role1(role_id)

equipment

equipment_idINTEGER(8)UNSIGNED
equipment_nameVARCHAR(40)NOT NULL
person_idINTEGER(8)UNSIGNED
PRIMARY KEY(equipment_id)
KEYIDX_equipment1(person_id,equipment_id)

equipment_type

equipment_type_idINTEGER(8)UNSIGNEDNOT NULL
equipment_typeVARCHAR(40)NOT NULL
equipment_categoryVARCHAR(40)NOT NULL
PRIMARY KEY(equipment_type_id)

COMMENT='Types include things like handie-talkie,first aid kit,boots,gloves. Categories include things like communications,PPE,medical,SAR.';

location

location_idINTEGER(8)UNSIGNEDNOT NULL
location_typeENUM('home,work,incident,organization,company,division,shelter,camp,command post,resource')NOT NULL
street_addressVARCHAR(100)
street_address2VARCHAR(100)
cityVARCHAR(50)
countyVARCHAR(50)
postal_codeVARCHAR(40)
longitudeVARCHAR(40)
latitudeVARCHAR(40)
last_updateDATE
organization_idINTEGER(8)UNSIGNED
PRIMARY KEY(location_id,location_type)
UNIQUE KEYIDX_location1(location_id,location_type)
KEYIDX_location2(organization_id,location_id)

person_location

person_idINTEGER(8)UNSIGNEDNOT NULL
location_idINTEGER(8)UNSIGNEDNOT NULL
location_typeENUM('home,work,other')NOT NULL
commentsTEXT
PRIMARY KEY(person_id,location_id,location_type)
KEYIDX_person_location1(location_id,location_type)
KEYIDX_person_location2(person_id)

incident

incident_idINTEGER(8)UNSIGNEDNOT NULL
plannedBOOL
descriptiionTEXT
commentsTEXT
activation_codeVARCHAR(10)
incident_primary_contactINTEGER(8)UNSIGNED
PRIMARY KEY(incident_id)
UNIQUE KEYIDX_incident1(incident_id)

COMMENT='planned events,training,emergencies,shelters,etc.';

tag

tag_idINTEGER(8)UNSIGNEDNOT NULL
descriptionTEXT
PRIMARY KEY(tag_id)
UNIQUE KEYIDX_tag1(tag_id)

COMMENT='Tags used in Twitter,etc.';

incident_tag

incident_idINTEGER(8)UNSIGNEDNOT NULL
tag_idINTEGER(8)UNSIGNEDNOT NULL
PRIMARY KEY(incident_id,tag_id)
KEYIDX_incident_tag1(incident_id)
KEYIDX_incident_tag2(tag_id)

event

event_idINTEGER(8)UNSIGNEDNOT NULL
category_idINTEGER(8)UNSIGNEDNOT NULL
event_type_idINTEGER(8)UNSIGNEDNOT NULL
descriptionTEXT
PRIMARY KEY(event_id,category_id,event_type_id)
KEYIDX_event1(category_id)
KEYIDX_event2(event_type_id)
UNIQUE KEYIDX_event3(event_id,category_id,event_type_id)

COMMENT='Things that happen before,during,after incidents. Command change,assessment,assignment,relief,demobilize,rehab';

event_category

category_idINTEGER(8)UNSIGNEDNOT NULL
category_nameVARCHAR(40)
descriptionTEXT
PRIMARY KEY(category_id)
UNIQUE KEYIDX_Entity_11(category_name)
UNIQUE KEYIDX_event_category2(category_id)

COMMENT='Categories refer to incident resources - people,communications,equipment,supplies.';

event_type

event_type_idINTEGER(8)UNSIGNEDNOT NULL
event_type_nameVARCHAR(40)
descriptionTEXT
PRIMARY KEY(event_type_id)
UNIQUE KEYIDX_event_type1(event_type_id)

COMMENT='Specific things that happen - assign people, make assessment, identify hazard,etc.';

incident_event

incident_event_idINTEGER(8)UNSIGNEDNOT NULL
incident_idINTEGER(8)UNSIGNED
event_idINTEGER(8)UNSIGNED
person_idINTEGER(8)UNSIGNED
role_idINTEGER(8)UNSIGNED
location_idINTEGER(8)UNSIGNED
resource_idINTEGER(8)UNSIGNED
location_typeENUM('home,work,incident,organization,company,division,shelter,camp,commandpost,resource')
category_idINTEGER(8)UNSIGNED
event_type_idINTEGER(8)UNSIGNED
starting_timeDATETIMENOT NULL
authorityINTEGER(8)UNSIGNED
communications_channelINTEGER(8)UNSIGNED
comm_channel_idINTEGER(8)UNSIGNED
tactical_nameVARCHAR(40)
ending_timeDATE
PRIMARY KEY(incident_event_id)
UNIQUE KEYIDX_incident_event1(incident_id,event_id)
KEYIDX_incident_event2(person_id)
KEYIDX_incident_event3(location_id,location_type)
KEYIDX_incident_event4(event_id,category_id,event_type_id)
KEYIDX_incident_event5(incident_id)
KEYIDX_incident_event6(authority)
KEYIDX_incident_event7(comm_channel_id)
KEYIDX_incident_event8(role_id)

COMMENT='Logging';

communication_channel

comm_channel_idINTEGER(8)UNSIGNEDNOT NULL
channel_typeENUM('HF,VHF,UHF,FRS')NOT NULL
toneENUM('#1 67.0,#2 71.9,#3 74.4,#4 77.0,#5 79.7,#6 82.5,#7 85.4,#8 88.5,#9 91.5,#10 94.8,#11 97.4,#12 100.0,#13 103.5,#14 107.2,#15 110.9,#16 114.8,#17 118.8,#18 123.0,#19 127.3,#20 131.8,#21 136.5,#22 141.3,#23 146.2,#24 151.4,#25 156.7,#26 162.2,#27 167.9,#28 173.8,#29 179.9,#30 186.2,#31 192.8,#32 203.5,#33 210.7,#34 218.1,#35 225.7,#36 233.6,#37 241.8,#38 250.3)
repeater_idVARCHAR(10)
repeater_offsetSMALLINT
PRIMARY KEY(comm_channel_id)
UNIQUE KEYIDX_communication_channel1(comm_channel_id))

COMMENT='Ham and FRS frequencies,PLs,etc.';

liaison

liaison_idINTEGER(8)UNSIGNEDNOT NULL
person_idINTEGER(8)UNSIGNEDNOT NULL
organization_id1INTEGER(8)UNSIGNEDNOT NULL
organization_id2INTEGER(8)UNSIGNEDNOT NULL
when_assignedDATETIMENOT NULL
when_relievedDATETIME
PRIMARY KEY(liaison_id)
UNIQUE KEYIDX_Entity_11(person_id,organization_id1,organization_id2,when_assigned)
KEYIDX_liaison2(person_id)
UNIQUE KEYIDX_liaison3(organization_id1)
UNIQUE KEYIDX_liaison4(organization_id2)

organization

organization_idINTEGER(8)UNSIGNEDNOT NULL
organization_nameVARCHAR(100)NOT NULL
organization_phoneINTEGER(8)UNSIGNED
organization_typeENUM('business,foundation,elected government,government agency,utility,public works,school,medical care,NGO,law enforcement,fire,EMS,religious,search and rescue,hazmat,agricultural,food,building supplies,demolition,long-term recovery,child care,senior care,human services,logistics,communications,disabled care')
vulnerableENUM('no,elderly,child,infant,disabled')
donorBOOL
parent_organizationINTEGER(8)UNSIGNED
geographic_scopeENUM('neighborhood,city,county,region,state,national,global')
geographic_scope_nameVARCHAR(40)
liaison_idINTEGER(8)UNSIGNED
page_idVARCHAR(40)NOT NULL
location_idINTEGER(8)UNSIGNED
phone_idINTEGER(8)UNSIGNED
PRIMARY KEY(organization_id)
UNIQUE KEYIDX_Entity_11(organization_name)
KEYIDX_organization2(organization_id,page_id)
KEYIDX_organization3(organization_id)
KEYIDX_organization4(organization_id,location_id)
KEYIDX_organization5(organization_id,phone_id)

person_language

person_idINTEGER(8)UNSIGNEDNOT NULL
language_idINTEGER(8)UNSIGNEDNOT NULL
fluencyENUM('mininal,conversational,advanced,native/fluent')NOT NULL
english_translatorBOOL
PRIMARY KEY(person_id)
UNIQUE KEYIDX_person_language1(person_id)

language

language_idINTEGER(8)UNSIGNEDNOT NULL
language_nameVARCHAR(50)NOT NULL
person_idINTEGER(8)UNSIGNED
PRIMARY KEY(language_id)
KEYIDX_language1(person_id)

organization_page

organization_idINTEGER(8)UNSIGNEDNOT NULL
page_idVARCHAR(40)NOT NULL
PRIMARY KEY(organization_id,page_id)
UNIQUE KEYIDX_organization_page1(organization_id,page_id)

page

page_idINTEGER(8)UNSIGNEDNOT NULL
urlVARCHAR(255)
descriptionVARCHAR(100)
page_typeENUM('Twitter,Facebook,YouTube,Wiki,Personal,Home')NOT NULL
organization_idINTEGER(8)UNSIGNED
person_idINTEGER(8)UNSIGNED
PRIMARY KEY(page_id)
KEYIDX_page1(organization_id,page_id)
KEYIDX_page2(person_id,page_id)

person_organization

person_idINTEGER(8)UNSIGNEDNOT NULL
organization_idENUM('PIO,Safety,Operations,Planning,Logistics,Intelligence,Finance/Admin')NOT NULL
role_idINTEGER(8)UNSIGNED
activeBOOL
leaderBOOL
membership_startedDATE
membership_endsDATE
PRIMARY KEY(person_id,organization_id)
UNIQUE KEYIDX_person_organization1(person_id)
UNIQUE KEYIDX_person_organization2(organization_id)

person_page

person_idINTEGER(8)UNSIGNEDNOT NULL
page_idINTEGER(8)UNSIGNEDNOT NULL
PRIMARY KEY(person_id,page_id)
UNIQUE KEYIDX_person_page1(person_id)
UNIQUE KEYIDX_person_page2(person_id,page_id)

person_equipment

person_idINTEGER(8)UNSIGNEDNOT NULL
equipment_idINTEGER(8)UNSIGNEDNOT NULL
needsINTEGERUNSIGNEDNOT NULLDEFAULT1
hasINTEGERUNSIGNEDNOT NULLDEFAULT0
PRIMARY KEY(person_id,equipment_id)
UNIQUE KEYIDX_person_equipment1(person_id)
UNIQUE KEYIDX_person_equipment2(person_id,equipment_id)

organization_location

organization_idINTEGER(8)UNSIGNEDNOT NULL
location_idINTEGER(8)UNSIGNEDNOT NULL
PRIMARY KEY(organization_id,location_id)
UNIQUE KEYIDX_organization_location1(organization_id,location_id)

phone

phone_idINTEGER(8)UNSIGNEDNOT NULL
area_codeINTEGER(4)UNSIGNEDNOT NULL
country_codeINTEGERUNSIGNEDNOT NULL
phone_numberINTEGER(8)UNSIGNEDNOT NULL
extensionINTEGER(6)UNSIGNED
service_providerVARCHAR(40)
confidentialBOOLNOT NULLDEFAULTFalse
primaryBOOL
24_7BOOL
last_updatedDATE
person_idINTEGER(8)UNSIGNED
organization_idINTEGER(8)UNSIGNED
PRIMARY KEY(phone_id)
KEYIDX_phone1(person_id,phone_id)
KEYIDX_phone2(organization_id,phone_id)

credential

credential_idINTEGER(8)UNSIGNEDNOT NULL
credential_nameVARCHAR(50)NOT NULL
credential_descriptionTEXTNOT NULL
credential_typeENUM('communications,ICS,EMS,SAR,CERT,vaccination')NOT NULL
person_idINTEGER(8)UNSIGNED
PRIMARY KEY(credential_id)
KEYIDX_credential1(person_id,credential_id)

person_credential

person_idINTEGER(8)UNSIGNEDNOT NULL
credential_idINTEGER(8)UNSIGNEDNOT NULL
issue_dateDATENOT NULL
expiresDATE
imageBLOB
verified_byINTEGER(8)UNSIGNEDNOT NULL
PRIMARY KEY(person_id,credential_id)
UNIQUE KEYIDX_person_credential1(person_id,credential_id)

person_phone

person_idINTEGER(8)UNSIGNEDNOT NULL
phone_idINTEGER(8)UNSIGNEDNOT NULL
phone_typeENUM('home,work,organization,other')NOT NULL
PRIMARY KEY(person_id,phone_id)
UNIQUE KEYIDX_person_phone1(person_id,phone_id)

organization_phone

organization_idINTEGER(8)UNSIGNEDNOT NULL
phone_idINTEGER(8)UNSIGNEDNOT NULL
phone_purposeVARCHAR(75)
PRIMARY KEY(organization_id,phone_id)
UNIQUE KEYIDX_organization_phone1(organization_id,phone_id)

Attachments (3)

Download all attachments as: .zip

Note: See TracWiki for help on using the wiki.