On October 29th 2012, Hurricane Sandy landed in New York City and devastated the area. In the weeks and months following, a widespread coordinated response was actively working to work to meet the needs of the affected communities.
We created a simple DBMS architecture to documents and track records of all the affected homes and families we’ve been working with. Using relational SQL tables and FileMaker Pro 12 for the user interface, the database uses two tables as the base units for the database:
Project File ~ Record for a single household, representing static information about the resident and their contact and demographic information, the state of the damage to their home/property, and the types of needs they have and assistance they are being provided with.
Project Update ~ Record representing a single encounter with a project. A Project Update (PU) can be of variable types; Canvass, Call, Visit, Work Dispatch, Supply Delivery, etc.
Project Files have a one-to-many relationship to Project Updates, i.e., one Project File normally has several Project Updates related to it, representing numerous interactions with the given project.
– Project Files: 1,313 records x 85 fields
- Fields: # of Adults,# of Children,# of Meals Delivered,# of Pets,# of Seniors,Activity Scale,Address,Address sans Apt,Address With ProjectID,Apt #,Best time to visit,Block,Borough,Building Type,Contact Type,Date Electricity Restored,Date Gas Restored,Date Scheduled,Days without electric,Days without heat,Demolition,FEMA_Amount,FEMA_Comment,FEMA_ID#,FEMA_Status,File UpToDate?,First Encounter,Flood Insurance_Had Before,Flood Insurance_Has Now,Followup Canvass,Food Stamps,Generator_Days,Generator?,geojson,Heater,Homebound?,Landlord_Tenant Dispute,Language,latitude,Legal Assistance,Location Name,longitude,Lost Job,Lot,Medical,Missing_ContactInfo,Primary Contact Email,Primary Contact Name,Primary Contact Phone,Project_ID,PU Count,PU Most Recent TS,RapidRepairs_Comment,RapidRepairs_Enrolled?,Rebuilding Together Application,RebuildingTogether_Applied?,Relationship to Primary Contact,Scheduled volunteers,Scheduled_WorkNeeded,Scheduled?,Secondary Contact Email,Secondary Contact Name,Secondary Contact Phone,Sheetrock_Comment,Sheetrock_Quantity,Sheetrock_Size,Status,Street Name,Street Number,Time Scheduled,Unemployed,Utilities notes,Work Needs_Assessed,Work Needs_Outstanding,
– Project Updates: 1,815 records x 31 fields
- Fields: Canvass_Schedule_Date,Canvass_Schedule_Time,Comments,Date,Meal Delivery,Needs Outstanding,ProjectID_fk,PU_FollowupComment,PU_ID_pk,PU_NumberOfVolunteers,PU_Scheduled_Date,PU_Scheduled_Time,PU_Scheduled_WorkNeeded,PU_Shift End Time,PU_Shift Start Time,PU_SpokeTo,PU_VolunteerName,PU_WhoCalled,Time,Timestamp,Update Type
– Volunteer Files: 3,987 records x 21 fields
- Fields: Address, Attendance, Availability, AvailabilityDisplay, Comments, Email, Gold Star, Has, Home State, Home Town, Language Skills, Minor, Name_Full, Name_Full with ID, OutOfTowner, Phone, Scan_Volunteer_Waiver, Signature, Special Skills, Volunteer_ID, Volunteer_Waiver_Date