safeSRC: BACK END
Authors:
Ben Francka, Brianna Bias, Dimitra Weinstein, Elise Muellerleile, Maria Ortiz-Lopez, Sofia Tejada
Community safety app that provides resources for 24/7 emergency services as a safer alternative to calling the police. Based on location, emergencies are filtered into selectable categories using a simple, clean UI to make navigation as accessible as possible when time is of the essence.
Project management board: https://miro.com/app/board/o9J_l1mE2t4=/
The organizations table exists to keep track of organizations that have been added to the database by service providers, to avoid duplicate entries and employ authorization.
id (PK) state_id (FK references states:id) city_id (FK references cities:id)
One to many with cities, states (organizations LEFT JOIN states WHERE organizations.state_id = states.id LEFT JOIN cities WHERE states.city_id = cities.id)
POST (oauth) PUT (oauth) Delete (oauth)
The states table provides a framework for expanding the app into a nationwide database by organizing the cities by their respective states.
id (PK) state_name (TEXT NOT NULL) city_id (FK references city:id)
One to many with cities (states LEFT JOIN cities WHERE states.city_id = cities.id)
GET GET: id
The cities table will provide the ability to search for individual services by city, and provides the key to joining the majority of the services tables.
id (PK) state_id (FK references states: id) city_name (TEXT NOT NULL)
One to many with housing_services, crime_services, mental_health_services, sexual_assault_services, crime services, Lgbtq_services, domestic_violence_services,substance_abuse services, elderly_assistance_services (cities LEFT JOIN housing_services WHERE cities.id = housing_services.city_id LEFT JOIN crime_services WHERE cities.id = crime_services.city_id LEFT JOIN lgbtq_services WHERE cities.id = lgbtq_services_city.id LEFT JOIN mental_health_services WHERE cities.id = mental_health_services.city_id LEFT JOIN sexual_assault_services WHERE cities.id = sexual_assault_services.city_id LEFT JOIN domestic_violece_services WHERE cities.id = domestic_violence_services.city.id LEFT JOIN elderly_assistance_services WHERE cities.id = elderly_assistnace_services.city_id LEFT JOIN substance_abuse_services WHERE cities.id = substance_abuse_services.city_id)
GET GET: id
The housing_services table stores organizations that provide said service.
id (PK) city_id (FK references cities: id) organization_id (FK references organizations: id) service_name (TEXT NOT NULL) description (TEXT) link (TEXT) phone_number (TEXT NOT NULL)
GET GET: id (service) GET: id (city) POST (oauth) PUT (oauth) DELETE (oauth)
The crime_services table stores organizations that provide said service.
id (PK) city_id (FK references cities: id) organization_id (FK references organizations: id) service_name (TEXT NOT NULL) description (TEXT) link (TEXT) phone_number (TEXT NOT NULL)
The lgbtq_services table stores organizations that provide said service.
id (PK) city_id (FK references cities: id) organization_id (FK references organizations: id) service_name (TEXT NOT NULL) description (TEXT) link (TEXT) phone_number (TEXT NOT NULL)
GET GET: id (service) GET: id (city) POST (oauth) PUT (oauth) DELETE (oauth)
The mental_health_services table stores organizations that provide said service.
id (PK) city_id (FK references cities: id) organization_id (FK references organizations: id) service_name (TEXT NOT NULL) description (TEXT) link (TEXT) phone_number (TEXT NOT NULL)
GET GET: id (service) GET: id (city) POST (oauth) PUT (oauth) DELETE (oauth)
The substance_abuse_services table stores organizations that provide said service.
id (PK) city_id (FK references cities: id) organization_id (FK references organizations: id) service_name (TEXT NOT NULL) description (TEXT) link (TEXT) phone_number (TEXT NOT NULL)
GET GET: id (service) GET: id (city) POST (oauth) PUT (oauth) DELETE (oauth)
The sexual_assault_services table stores organizations that provide said service.
id (PK) city_id (FK references cities: id) organization_id (FK references organizations: id) service_name (TEXT NOT NULL) description (TEXT) link (TEXT) phone_number (TEXT NOT NULL)
GET GET: id (service) GET: id (city) POST (oauth) PUT (oauth) DELETE (oauth)
The elderly_assistance_services table stores organizations that provide said service.
id (PK) city_id (FK references cities: id) organization_id (FK references organizations: id) service_name (TEXT NOT NULL) description (TEXT) link (TEXT) phone_number (TEXT NOT NULL)
GET GET: id (service) GET: id (city) POST (oauth) PUT (oauth) DELETE (oauth)
The domestic_violence_services table stores organizations that provide said service.
id (PK) city_id (FK references cities: id) organization_id (FK references organizations: id) service_name (TEXT NOT NULL) description (TEXT) link (TEXT) phone_number (TEXT NOT NULL)
GET GET: id (service) GET: id (city) POST (oauth) PUT (oauth) DELETE (oauth)
Wire frames of safeSRC:
