Changes between Version 5 and Version 6 of Domain/EmergencyManagement/IncidentManagementSystems/IncidentCommandSystem(UnitedStates)


Ignore:
Timestamp:
03/16/11 17:06:08 (11 years ago)
Author:
Nick Arnett
Comment:

Revised schema

Legend:

Unmodified
Added
Removed
Modified
  • Domain/EmergencyManagement/IncidentManagementSystems/IncidentCommandSystem(UnitedStates)

    v5 v6  
    4242
    4343== Table structures ==
    44 
    45 '''PERSON'''
    46 ||person_id||PK|||
    47 ||person_title||CHAR||
    48 ||first_name||CHAR||
    49 ||nickname||CHAR||
    50 ||last_name||CHAR||
     44'''person'''
     45||person_id||INTEGER(8)||UNSIGNED||NOT NULL||
     46||person_title||VARCHAR(40)||
     47||first_name||VARCHAR(40)||NOT NULL||
     48||last_name||VARCHAR(40)||NOT NULL||
     49||nickname||VARCHAR(40)||
    5150||photo||BLOB||
    5251||physical_limitations||TEXT||
    5352||comments||TEXT||
    54 ||gender||ENUM(m, f)||
     53||gender||ENUM(m,f)||
    5554||birthdate||DATE||
    56 ||status||ENUM(interested, pending, active, related) # related is for people who are in the database but not part of a team||
    57 ||emergency_contact||FK person_id||
    58 
    59 '''ROLE'''
    60 ||role_id||PK||
    61 ||ICS_level||ENUM(Command, Section, Division, Individual)||
    62 ||ICS_role||ENUM(PIO, Safety, Operations, Planning, Logistics, Intelligence, Finance/Admin)||
    63 
    64 '''LIAISON'''
    65 
    66 # Could belong in incident_event, but it is a persistent relationship sometimes.
    67 ||liaison_id||PK, FK||
    68 ||person_id||PK, FK||
    69 ||organization_id1||PK, FK||
    70 ||organization_id2||PK, FK||
    71 ||when_assigned||DATETIME||
    72 ||when_unassigned||DATETIME||
    73 
    74 '''ACCESS_CONTROL'''
    75 TBD
    76 
    77 '''EQUIPMENT'''
    78 ||equipment_id||PK||
    79 ||equipment_name||CHAR||
    80 ||equipment_type||ENUM (HT, mobile radio, antenna, first aid kit, boots, backpack...)||
    81 ||equipment_category||ENUM (communications, PPE, medical, SAR...)||
    82 
    83 '''PERSON_EQUIPMENT'''
    84 
    85 # Equipment that volunteers have or should have.
    86 ||person_id||PK, FK||
    87 ||equipment_id||PK, FK||
    88 ||needs||INT||
    89 ||has||INT||
    90 
    91 '''LOCATION'''
    92 ||location_id||PK||
    93 ||location_type||ENUM (home, work, incident, organization, company, division)||
    94 ||street_address||CHAR||
    95 ||street_address2||CHAR||
    96 ||city|CHAR|||
    97 ||county||CHAR||
    98 ||state/province||CHAR||
    99 ||country||CHAR||
    100 ||postal_code||CHAR||
    101 ||longitude||||
    102 ||latitude||||
    103 ||last_updated||(DATE)||
    104 
    105 '''PERSON_LOCATION'''
    106 ||person_id||PK, FK||
    107 ||location_id||PK, FK||
    108 ||location_type||ENUM(home, work, organization)||
     55||status||ENUM('interested_volunteer,pending_volunteer,active_volunteer,inactive_volunteer,liason,contact')||NOT NULL||
     56||emergency_contact||INTEGER(8)||UNSIGNED||
     57||credential_id||INTEGER(8)||UNSIGNED||
     58||phone_id||INTEGER(8)||UNSIGNED||
     59||PRIMARY KEY||(person_id)||
     60||UNIQUE KEY||IDX_person1(person_id)||
     61||KEY||IDX_person2(person_id,credential_id)||
     62||KEY||IDX_person3(person_id,phone_id)||
     63
     64'''role'''
     65||role_id||INTEGER(8)||UNSIGNED||NOT NULL||
     66||ics_level||ENUM('Command,Section,Division,Individual')||
     67||ics_role||ENUM('PIO,Safety,Operations,Planning,Logistics,Intelligence,Finance/Admin')||
     68||PRIMARY KEY||(role_id)||
     69||UNIQUE KEY||IDX_role1(role_id)||
     70
     71'''equipment'''
     72||equipment_id||INTEGER(8)||UNSIGNED||
     73||equipment_name||VARCHAR(40)||NOT NULL||
     74||person_id||INTEGER(8)||UNSIGNED||
     75||PRIMARY KEY||(equipment_id)||
     76||KEY||IDX_equipment1(person_id,equipment_id)||
     77
     78'''equipment_type'''
     79||equipment_type_id||INTEGER(8)||UNSIGNED||NOT NULL||
     80||equipment_type||VARCHAR(40)||NOT NULL||
     81||equipment_category||VARCHAR(40)||NOT NULL||
     82||PRIMARY KEY||(equipment_type_id)||
     83COMMENT='Types include things like handie-talkie,first aid kit,boots,gloves. Categories include things like communications,PPE,medical,SAR.';
     84
     85'''location'''
     86||location_id||INTEGER(8)||UNSIGNED||NOT NULL||
     87||location_type||ENUM('home,work,incident,organization,company,division,shelter,camp,command post,resource')||NOT NULL||
     88||street_address||VARCHAR(100)||
     89||street_address2||VARCHAR(100)||
     90||city||VARCHAR(50)||
     91||county||VARCHAR(50)||
     92||postal_code||VARCHAR(40)||
     93||longitude||VARCHAR(40)||
     94||latitude||VARCHAR(40)||
     95||last_update||DATE||
     96||organization_id||INTEGER(8)||UNSIGNED||
     97||PRIMARY KEY||(location_id,location_type)||
     98||UNIQUE KEY||IDX_location1(location_id,location_type)||
     99||KEY||IDX_location2(organization_id,location_id)||
     100
     101'''person_location'''
     102||person_id||INTEGER(8)||UNSIGNED||NOT NULL||
     103||location_id||INTEGER(8)||UNSIGNED||NOT NULL||
     104||location_type||ENUM('home,work,other')||NOT NULL||
    109105||comments||TEXT||
    110 
    111 '''PERSON_ORGANIZATION'''
    112 
    113 # Tracks people's membership in an organization
    114 ||person_id||PK, FK||
    115 ||organization_id||PK, FK||
    116 ||active||BOOLEAN
    117 ||leader||BOOLEAN
    118 ||role_id||FK||
     106||PRIMARY KEY||(person_id,location_id,location_type)||
     107||KEY||IDX_person_location1(location_id,location_type)||
     108||KEY||IDX_person_location2(person_id)||
     109
     110'''incident'''
     111||incident_id||INTEGER(8)||UNSIGNED||NOT NULL||
     112||planned||BOOL||
     113||descriptiion||TEXT||
     114||comments||TEXT||
     115||activation_code||VARCHAR(10)||
     116||incident_primary_contact||INTEGER(8)||UNSIGNED||
     117||PRIMARY KEY||(incident_id)||
     118||UNIQUE KEY||IDX_incident1(incident_id)||
     119COMMENT='planned events,training,emergencies,shelters,etc.';
     120
     121'''tag'''
     122||tag_id||INTEGER(8)||UNSIGNED||NOT NULL||
     123||description||TEXT||
     124||PRIMARY KEY||(tag_id)||
     125||UNIQUE KEY||IDX_tag1(tag_id)||
     126COMMENT='Tags used in Twitter,etc.';
     127
     128'''incident_tag'''
     129||incident_id||INTEGER(8)||UNSIGNED||NOT NULL||
     130||tag_id||INTEGER(8)||UNSIGNED||NOT NULL||
     131||PRIMARY KEY||(incident_id,tag_id)||
     132||KEY||IDX_incident_tag1(incident_id)||
     133||KEY||IDX_incident_tag2(tag_id)||
     134
     135'''event'''
     136||event_id||INTEGER(8)||UNSIGNED||NOT NULL||
     137||category_id||INTEGER(8)||UNSIGNED||NOT NULL||
     138||event_type_id||INTEGER(8)||UNSIGNED||NOT NULL||
     139||description||TEXT||
     140||PRIMARY KEY||(event_id,category_id,event_type_id)||
     141||KEY||IDX_event1(category_id)||
     142||KEY||IDX_event2(event_type_id)||
     143||UNIQUE KEY||IDX_event3(event_id,category_id,event_type_id)||
     144COMMENT='Things that happen before,during,after incidents. Command change,assessment,assignment,relief,demobilize,rehab';
     145
     146'''event_category'''
     147||category_id||INTEGER(8)||UNSIGNED||NOT NULL||
     148||category_name||VARCHAR(40)||
     149||description||TEXT||
     150||PRIMARY KEY||(category_id)||
     151||UNIQUE KEY||IDX_Entity_11(category_name)||
     152||UNIQUE KEY||IDX_event_category2(category_id)||
     153COMMENT='Categories refer to incident resources - people,communications,equipment,supplies.';
     154
     155'''event_type'''
     156||event_type_id||INTEGER(8)||UNSIGNED||NOT NULL||
     157||event_type_name||VARCHAR(40)||
     158||description||TEXT||
     159||PRIMARY KEY||(event_type_id)||
     160||UNIQUE KEY||IDX_event_type1(event_type_id)||
     161COMMENT='Specific things that happen - assign people, make assessment, identify hazard,etc.';
     162
     163'''incident_event'''
     164||incident_event_id||INTEGER(8)||UNSIGNED||NOT NULL||
     165||incident_id||INTEGER(8)||UNSIGNED||
     166||event_id||INTEGER(8)||UNSIGNED||
     167||person_id||INTEGER(8)||UNSIGNED||
     168||role_id||INTEGER(8)||UNSIGNED||
     169||location_id||INTEGER(8)||UNSIGNED||
     170||resource_id||INTEGER(8)||UNSIGNED||
     171||location_type||ENUM('home,work,incident,organization,company,division,shelter,camp,command||post,resource')||
     172||category_id||INTEGER(8)||UNSIGNED||
     173||event_type_id||INTEGER(8)||UNSIGNED||
     174||starting_time||DATETIME||NOT NULL||
     175||authority||INTEGER(8)||UNSIGNED||
     176||communications_channel||INTEGER(8)||UNSIGNED||
     177||comm_channel_id||INTEGER(8)||UNSIGNED||
     178||tactical_name||VARCHAR(40)||
     179||ending_time||DATE||
     180||PRIMARY KEY||(incident_event_id)||
     181||UNIQUE KEY||IDX_incident_event1(incident_id,event_id)||
     182||KEY||IDX_incident_event2(person_id)||
     183||KEY||IDX_incident_event3(location_id,location_type)||
     184||KEY||IDX_incident_event4(event_id,category_id,event_type_id)||
     185||KEY||IDX_incident_event5(incident_id)||
     186||KEY||IDX_incident_event6(authority)||
     187||KEY||IDX_incident_event7(comm_channel_id)||
     188||KEY||IDX_incident_event8(role_id)||
     189COMMENT='Logging';
     190
     191'''communication_channel'''
     192||comm_channel_id||INTEGER(8)||UNSIGNED||NOT NULL||
     193||channel_type||ENUM('HF,VHF,UHF,FRS')||NOT NULL||
     194||tone||ENUM('#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)||||
     195||repeater_id||VARCHAR(10)||
     196||repeater_offset||SMALLINT||
     197||PRIMARY KEY||(comm_channel_id)||
     198||UNIQUE KEY||IDX_communication_channel1(comm_channel_id))||
     199COMMENT='Ham and FRS frequencies,PLs,etc.';
     200
     201'''liaison'''
     202||liaison_id||INTEGER(8)||UNSIGNED||NOT NULL||
     203||person_id||INTEGER(8)||UNSIGNED||NOT NULL||
     204||organization_id1||INTEGER(8)||UNSIGNED||NOT NULL||
     205||organization_id2||INTEGER(8)||UNSIGNED||NOT NULL||
     206||when_assigned||DATETIME||NOT NULL||
     207||when_relieved||DATETIME||
     208||PRIMARY KEY||(liaison_id)||
     209||UNIQUE KEY||IDX_Entity_11(person_id,organization_id1,organization_id2,when_assigned)||
     210||KEY||IDX_liaison2(person_id)||
     211||UNIQUE KEY||IDX_liaison3(organization_id1)||
     212||UNIQUE KEY||IDX_liaison4(organization_id2)||
     213
     214'''organization'''
     215||organization_id||INTEGER(8)||UNSIGNED||NOT NULL||
     216||organization_name||VARCHAR(100)||NOT NULL||
     217||organization_phone||INTEGER(8)||UNSIGNED||
     218||organization_type||ENUM('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')||
     219||vulnerable||ENUM('no,elderly,child,infant,disabled')||
     220||donor||BOOL||
     221||parent_organization||INTEGER(8)||UNSIGNED||
     222||geographic_scope||ENUM('neighborhood,city,county,region,state,national,global')||
     223||geographic_scope_name||VARCHAR(40)||
     224||liaison_id||INTEGER(8)||UNSIGNED||
     225||page_id||VARCHAR(40)||NOT NULL||
     226||location_id||INTEGER(8)||UNSIGNED||
     227||phone_id||INTEGER(8)||UNSIGNED||
     228||PRIMARY KEY||(organization_id)||
     229||UNIQUE KEY||IDX_Entity_11(organization_name)||
     230||KEY||IDX_organization2(organization_id,page_id)||
     231||KEY||IDX_organization3(organization_id)||
     232||KEY||IDX_organization4(organization_id,location_id)||
     233||KEY||IDX_organization5(organization_id,phone_id)||
     234
     235'''person_language'''
     236||person_id||INTEGER(8)||UNSIGNED||NOT NULL||
     237||language_id||INTEGER(8)||UNSIGNED||NOT NULL||
     238||fluency||ENUM('mininal,conversational,advanced,native/fluent')||NOT NULL||
     239||english_translator||BOOL||
     240||PRIMARY KEY||(person_id)||
     241||UNIQUE KEY||IDX_person_language1(person_id)||
     242
     243'''language'''
     244||language_id||INTEGER(8)||UNSIGNED||NOT NULL||
     245||language_name||VARCHAR(50)||NOT NULL||
     246||person_id||INTEGER(8)||UNSIGNED||
     247||PRIMARY KEY||(language_id)||
     248||KEY||IDX_language1(person_id)||
     249
     250'''organization_page'''
     251||organization_id||INTEGER(8)||UNSIGNED||NOT NULL||
     252||page_id||VARCHAR(40)||NOT NULL||
     253||PRIMARY KEY||(organization_id,page_id)||
     254||UNIQUE KEY||IDX_organization_page1(organization_id,page_id)||
     255
     256'''page'''
     257||page_id||INTEGER(8)||UNSIGNED||NOT NULL||
     258||url||VARCHAR(255)||
     259||description||VARCHAR(100)||
     260||page_type||ENUM('Twitter,Facebook,YouTube,Wiki,Personal,Home')||NOT NULL||
     261||organization_id||INTEGER(8)||UNSIGNED||
     262||person_id||INTEGER(8)||UNSIGNED||
     263||PRIMARY KEY||(page_id)||
     264||KEY||IDX_page1(organization_id,page_id)||
     265||KEY||IDX_page2(person_id,page_id)||
     266
     267'''person_organization'''
     268||person_id||INTEGER(8)||UNSIGNED||NOT NULL||
     269||organization_id||ENUM('PIO,Safety,Operations,Planning,Logistics,Intelligence,Finance/Admin')||NOT NULL||
     270||role_id||INTEGER(8)||UNSIGNED||
     271||active||BOOL||
     272||leader||BOOL||
    119273||membership_started||DATE||
    120 ||membership_expires||DATE||
    121 
    122 '''EMAIL'''
    123 ||email_id||PK||
    124 ||email_address||CHAR||
    125 ||email_type||ENUM(normal, pager)||
     274||membership_ends||DATE||
     275||PRIMARY KEY||(person_id,organization_id)||
     276||UNIQUE KEY||IDX_person_organization1(person_id)||
     277||UNIQUE KEY||IDX_person_organization2(organization_id)||
     278
     279'''person_page'''
     280||person_id||INTEGER(8)||UNSIGNED||NOT NULL||
     281||page_id||INTEGER(8)||UNSIGNED||NOT NULL||
     282||PRIMARY KEY||(person_id,page_id)||
     283||UNIQUE KEY||IDX_person_page1(person_id)||
     284||UNIQUE KEY||IDX_person_page2(person_id,page_id)||
     285
     286'''person_equipment'''
     287||person_id||INTEGER(8)||UNSIGNED||NOT NULL||
     288||equipment_id||INTEGER(8)||UNSIGNED||NOT NULL||
     289||needs||INTEGER||UNSIGNED||NOT NULL||DEFAULT||1||
     290||has||INTEGER||UNSIGNED||NOT NULL||DEFAULT||0||
     291||PRIMARY KEY||(person_id,equipment_id)||
     292||UNIQUE KEY||IDX_person_equipment1(person_id)||
     293||UNIQUE KEY||IDX_person_equipment2(person_id,equipment_id)||
     294
     295'''organization_location'''
     296||organization_id||INTEGER(8)||UNSIGNED||NOT NULL||
     297||location_id||INTEGER(8)||UNSIGNED||NOT NULL||
     298||PRIMARY KEY||(organization_id,location_id)||
     299||UNIQUE KEY||IDX_organization_location1(organization_id,location_id)||
     300
     301'''phone'''
     302||phone_id||INTEGER(8)||UNSIGNED||NOT NULL||
     303||area_code||INTEGER(4)||UNSIGNED||NOT NULL||
     304||country_code||INTEGER||UNSIGNED||NOT NULL||
     305||phone_number||INTEGER(8)||UNSIGNED||NOT NULL||
     306||extension||INTEGER(6)||UNSIGNED||
     307||service_provider||VARCHAR(40)||
     308||confidential||BOOL||NOT NULL||DEFAULT||False||
     309||primary||BOOL||
     310||24_7||BOOL||
    126311||last_updated||DATE||
    127 
    128 '''PERSON_EMAIL'''
    129 ||person_id||PK, FK||
    130 ||email_id||PK, FK||
    131 
    132 '''PHONE'''
    133 # Can belong to person, organization, resource
    134 ||phone_id||PK||
    135 ||phone_type||ENUM(home, work, mobile, other)||
    136 ||country_code||INT||
    137 ||area_code||INT||
    138 ||phone_number||INT||
    139 ||extension||INT||
    140 ||service_provider||CHAR||
    141 ||confidential||BOOLEAN||
    142 ||primary||BOOLEAN||
    143 ||24_7||BOOLEAN||
    144 ||last_updated||DATE||
    145 ||organization_id||FK||
    146 ||resource_id||FK||
    147 ||time_of_day||ENUM(any, day, night)||
    148 ||comments||TEXT||
    149 
    150 '''PERSON_PHONE'''
    151 ||person_id||PK, FK||
    152 ||phone_id||PK, FK||
    153 
    154 '''CREDENTIAL'''
    155 
    156 # Any kind of certificate, training, other prerequisite associated with a person
    157 ||credential_id||PK||
    158 ||credential_name||CHAR||
    159 ||credential_description||TEXT||
    160 ||credential_type||ENUM(communications, ICS, EMS, SAR, CERT, vaccination...)||
    161 
    162 '''PERSON_CREDENTIAL'''
    163 ||person_id||PK, FK||
    164 ||credential_id||PK, FK||
    165 ||issuing_organization_id||PK, FK||
    166 ||completion_date||DATE||
    167 ||verified_by||FK person_id||
    168 ||verified_date||DATE||
    169 ||credential_expires||DATE||
    170 ||credential_certificate_id||CHAR||
    171 
    172 '''INCIDENT'''
    173 ||incident_id||PK||
    174 ||incident_name||TEXT||
    175 ||planned||BOOLEAN||
    176 ||incident_description||TEXT||
    177 ||incident_comments||TEXT||
    178 ||activation_code||CHAR||
    179 ||incident_contact||FK person_id||
    180 ||incident_type_id||FK||
    181 ||location_id||FK||
    182 ||address_id||FK||
    183 ||incident_page||FK page_id||
    184 ||twitter_hashtag||||
    185 
    186 '''INCIDENT_TYPES'''
    187 ||incident_type_id||PK||
    188 ||incident_category||ENUM (preparedness, response)||
    189 ||incident_type||ENUM(fire, flood, earthquake, medical, evacuation, terrorism, other emergency, critical incident debrief, operational debrief, class, seminar, workshop, game, training, table-top exercise, functional exercise, full-scale exercise)||
    190 ||incident_geography||ENUM(neighborhood, city, county, region, state, national, global)||
    191 
    192 '''PAGE'''
    193 # web pages
    194 ||page_id||PK||
    195 ||URL||CHAR||
    196 ||title||CHAR||
    197 ||description||TEXT||
    198 ||last_reviewed||DATE||
    199 
    200 '''PERSON_PAGE'''
    201 ||person_id||PK, FK||
    202 ||page_id||PK, FK||
    203 ||page_type||ENUM(Personal, Twitter, Facebook, YouTube, other||
    204 
    205 '''PERSON_INCIDENT'''
    206 
    207 # This belongs with logging...
    208 ||person_id||PK, FK||
    209 ||incident_id||PK, FK||
    210 ||notified||DATETIME||
    211 ||committed||DATETIME||
    212 ||committed_by||FK person_id ||
    213 ||arrived||DATETIME||
    214 ||departed||DATETIME||
    215 ||met_requirements||BOOLEAN||
    216 ||verified_by||FK person_id||
    217 
    218 '''INCIDENT_ROLE'''
    219 
    220 # Volunteer slots to be filled
    221 ||incident_id||PK, FK||
    222 ||role_id||PK, FK||
    223 ||shift_id||PK, INT||
    224 ||starting||DATETIME||
    225 ||ending||DATETIME||
    226 ||people_needed||INT||
    227 
    228 '''INCIDENT_EVENT'''
    229 
    230 # Logging - most of this will probably happen on paper, then transcribed
    231 ||event_id||PK||
    232 ||event_type||ENUM(person, resource, objective, assessment, situation report, status_report, strategy, intelligence, incident location, incident command, incident base, joint information center)||
    233 ||incident_id||FK||
    234 ||person_id||FK||
    235 ||person_event||ENUM (notified, en-route, checked_in, assigned, rehab, demobilized, available)||
    236 ||role_id||FK||
    237 ||address_id||FK||
    238 ||resource_id||FK||
    239 ||authority||FK person_id||
    240 ||incident_timestamp||DATETIME||
    241 ||public||BOOLEAN||
    242 ||incident_communications_primary||CHAR||
    243 ||incident_communications_secondary||CHAR||
    244 ||comment||TEXT||
    245 
    246 '''ROLE_PREREQUISITE'''
    247 
    248 # qualifications, training, etc.
    249 ||incident_id||PK, FK||
    250 ||role_id||PK, FK||
    251 ||prerequisite_id||FK||
    252 ||required||ENUM(True, recommended)||
    253 
    254 '''CURRICULUM'''
    255 
    256 # Series of classes that typically would be taken together
    257 # Curriculum itself becomes a prerequisite
    258 ||curriculum_id||PK||
    259 ||curriculum_name||CHAR||
    260 ||incident_id||FK||
    261 ||recommended_sequence||INT||
    262 ||required||ENUM(Yes, Recommended||
    263 
    264 '''ASSIGNMENT'''
    265 ||assignment_id||PK||
    266 ||incident_id||PK, FK||
    267 ||assignment_description||TEXT||
    268 
    269 '''ASSIGNMENT_PERSON'''
    270 
    271 # Move into logging...
    272 ||assignment_id||PK, FK||
    273 ||person_id||PK, FK||
    274 ||assigned_when||DATETIME||
    275 ||removed_when||DATETIME||
    276 
    277 '''ORGANIZATION'''
    278 
    279 # Companies, government agencies, etc. - association or group of people with like objectives
    280 ||organization_id||PK||
    281 ||organization_name||CHAR||
    282 ||organization_phone||FK||
    283 ||organization_page||FK||
    284 ||organization_type||ENUM(company, foundation, government agency, NGO, responder)||
    285 ||organization_purpose||ENUM(medical, supplies, elder care, child care, church, shelter, school, public works, communications, transportation, firefighting, emergency management, mass care/emergency assistance/housing/human services, logistics, public health, SAR, hazardous materials, agriculture/natural resources, energy, law enforcement, long-term recovery)||
    286 ||vulnerable_population||ENUM (children, elderly, disabled)||
    287 ||donor||BOOLEAN||
    288 ||parent_organization||FK organization_id||
    289 ||organization_scope||ENUM(neighborhood, city, county, state, national, global)||
    290 
    291 '''ORGANIZATION_SERVICE_AREA'''
    292 
    293 # Geographic description of areas that an organization serves: TBD
    294 
    295 '''ORGANIZATION_PAGE'''
    296 ||organization_id||PK, FK||
    297 ||page_id||PK, FK||
    298 ||description||TEXT||
    299 
    300 '''RESOURCE'''
    301 ||resource_id||PK||
    302 ||resource_name||CHAR||
    303 ||resource_page||FK page_id||
    304 ||resource_type||ENUM(web site, park, storage, fire station, gas station, atm, water)||
    305 ||resource_scope||ENUM(neighborhood, city, county, state, national, global)||
    306 ||category||ENUM(training, operations, discussion, communications)||
    307 
    308 '''RESOURCE_LOCATION'''
    309 ||resource_id||PK, FK||
    310 ||location_id||PK, FK||
    311 
    312 '''RESOURCE_PERSON'''
    313 ||resource_id||PK, FK||
    314 ||person_id||PK, FK||
    315 ||primary_contact||BOOLEAN
    316 
    317 '''RESOURCE_ORGANIZATION'''
    318 ||resource_id||PK, FK||
    319 ||organization_id||PK, FK||
    320 
    321 '''DIVISION'''
    322 
    323 # this needs to be generalized to include neighborhood teams?
    324 ||division_id||PK||
    325 ||division_number||||
    326 ||division_name||CHAR||
    327 ||division_command_address_id||FK||
    328 ||primary_frs_channel||||
    329 ||primary_frs_pl||||
    330 ||tactical_frs_channel||||
    331 ||tactical_frs_pl||||
     312||person_id||INTEGER(8)||UNSIGNED||
     313||organization_id||INTEGER(8)||UNSIGNED||
     314||PRIMARY KEY||(phone_id)||
     315||KEY||IDX_phone1(person_id,phone_id)||
     316||KEY||IDX_phone2(organization_id,phone_id)||
     317
     318'''credential'''
     319||credential_id||INTEGER(8)||UNSIGNED||NOT NULL||
     320||credential_name||VARCHAR(50)||NOT NULL||
     321||credential_description||TEXT||NOT NULL||
     322||credential_type||ENUM('communications,ICS,EMS,SAR,CERT,vaccination')||NOT NULL||
     323||person_id||INTEGER(8)||UNSIGNED||
     324||PRIMARY KEY||(credential_id)||
     325||KEY||IDX_credential1(person_id,credential_id)||
     326
     327'''person_credential'''
     328||person_id||INTEGER(8)||UNSIGNED||NOT NULL||
     329||credential_id||INTEGER(8)||UNSIGNED||NOT NULL||
     330||issue_date||DATE||NOT NULL||
     331||expires||DATE||
     332||image||BLOB||
     333||verified_by||INTEGER(8)||UNSIGNED||NOT NULL||
     334||PRIMARY KEY||(person_id,credential_id)||
     335||UNIQUE KEY||IDX_person_credential1(person_id,credential_id)||
     336
     337'''person_phone'''
     338||person_id||INTEGER(8)||UNSIGNED||NOT NULL||
     339||phone_id||INTEGER(8)||UNSIGNED||NOT NULL||
     340||phone_type||ENUM('home,work,organization,other')||NOT NULL||
     341||PRIMARY KEY||(person_id,phone_id)||
     342||UNIQUE KEY||IDX_person_phone1(person_id,phone_id)||
     343
     344'''organization_phone'''
     345||organization_id||INTEGER(8)||UNSIGNED||NOT NULL||
     346||phone_id||INTEGER(8)||UNSIGNED||NOT NULL||
     347||phone_purpose||VARCHAR(75)||
     348||PRIMARY KEY||(organization_id,phone_id)||
     349||UNIQUE KEY||IDX_organization_phone1(organization_id,phone_id)||