Designing and Developing a Relational Database for SafeSpace

My Startup Idea Developed Through Pratt Institute's Ignition Lab

Project Overview

Project Description

During my entrepreneurship program at Pratt Institute’s Ignition Lab, we learned the foundational steps to launch a business, such as interviewing target customers, doing market research, developing a business roadmap, building a Minimum Viable Product, and more.

After identifying a gap in the mental health space, I envisioned a startup company, SafeSpace, whose mission is to revolutionize mental healthcare by creating a safe space for individuals with PTSD to understand, manage, and overcome their challenges through innovative technology and personalized support. More specifically, leveraging biofeedback, passive sensing, and machine learning algorithms would help users gain awareness of their symptoms, provide them with real-time, personalized interventions based on their physiological responses to the environment, and provide a medium that allows practitioners track their patient’s progress. These interventions could include various holistic approaches such as meditation, sound therapy, specific natural remedies, or breathing exercises.

This project focused on the design and development of a database for SafeSpace using Lucid Chart and MySQL, which serves as the foundation for managing the complex data streams that will be crucial for the application’s functionality and future growth.

Project Significance

This database design demonstrates the potential for managing and leveraging data to support personalized PTSD treatment. It lays the groundwork for further development as SafeSpace evolves its product offerings and expands its target audience.

Tools

Key Considerations

Database Accessibility & Privacy

The database design prioritizes accessibility for different user groups. Data privacy is a critical concern. While user data offers valuable insights, addressing patient concerns regarding data privacy is essential. This project acknowledges this challenge and seeks further exploration on how to integrate data privacy considerations throughout the database design process.

Needs Analysis

Before designing the database, it was crucial to identify the information needs of various user groups:

Users: Track triggers, environmental responses, and progress with managing PTSD symptoms. Gain insight into intervention methods.
Practitioners: Monitor patient progress, view patient information (conditions, symptoms, feedback data), analyze intervention effectiveness, gain insights into holistic PTSD treatment approaches, create and update treatment plans, access existing treatments from research, record visit notes, and potentially connect with other practitioners treating patients using wearables.
Company: Manage business aspects like employee records, product development, marketing strategies, and data-driven insights for improvement. This was considered a future consideration and omitted in this project to narrow down the scope due to time constraints.

Entity-Relationship Modeling (ERM)

Conceptual Data Modeling: The Process

Identified the core entities relevant to the application’s functionality, which represent what SafeSpace will store information about.
Defined the attributes associated with each entity, which represent the specific characteristics or properties captured for each entity instance.
Defined relationships between entities (e.g., many-to-many relationship between Condition & Symptom).
Used Crow’s Foot Notation to create a diagram of the conceptual data model on Lucidchart.

Logical Data Modeling: The Process

The next step involved transforming the conceptual model into a more robust logical data model. This logical model aimed to minimize redundancy and improve data integrity. This was achieved through normalization.

Targeted the 3rd Normal Form (3NF), to decompose entities with multi-valued attributes.
In a relational database, a table can only have a one-to-many or many-to-one relationship with another table, but real-world scenarios frequently have many-to-many relationships. Associative entities were introduced to model many-to-many relationships between entities (e.g., a Patient with multiple Conditions). These entities link the primary entities and hold additional attributes relevant to the specific relationship (e.g., Patient_Condition entity captures the severity of a particular condition for a patient).
Eliminated partial dependencies by ensuring that all non-key attributes within an entity depend on the entire primary key. This helps maintain data consistency and avoid situations where updates to one part of the data might lead to inconsistencies in other parts.
Following the normalization process, used Crow’s Foot Notation to create an Entity-Relationship diagram of the logical data model on Lucidchart.

By following these steps, I iteratively refined the conceptual model into a well-structured logical data model suitable for implementation in a relational database management system.

Key Changes Made to Database

Based on input from professor, I changed my tables to include a surrogate primary key because if they have a primary key with two foreign keys, it only allows for one instance.
Added a many-to-many relationship between Practitioner and Speciality as many practitioners can have many specialties.
Moved the Credential entity to be linked directly to Practitioner rather than Specialty because specialty may not have a license number.
Removed the connection between Patient and Physiological_Response as tables can be joined to retrieve this information.
Made Condition_to_Symptom optional.
Made Feedback to Manual _Input M:N instead of 1:M as there can be many instances on both sides.
Expanded ‘address’ attributes to the individual components to avoid issues with updates later on.
Made Patient to Manual_Input optional as users may not actually input manual data.
Made Wearable_Device & Physiological_Response M:N instead of 1:M as there are many instances from both sides.
Made Patient & Wearable_Device optional as users may not need the device to use the app – can receive feedback just based on manual input.

The well-structured logical data model that was developed served as the blueprint for the actual database. The next step was to translate this blueprint into a physical database schema suitable for implementation in MySQL.

From Design to Implementation: Building the SafeSpace Database

Building the Database Schema in MySQL

This translation involves defining the structure of each table within the database. Each table represents a specific entity from the logical model, and its columns correspond to the entity’s attributes. The data types for each column were defined to ensure proper data storage and manipulation.

Key Components of the Schema

Primary Keys

Each table required a unique identifier for each record in the table to ensure efficient data retrieval and modification.

Foreign Keys

Foreign keys were used to establish relationships between the tables. They reference the primary key of another table in order to link related data across tables. For example, a foreign key in the Patient_Condition table references the primary key in the Patient table and another in the Condition table, effectively connecting patients to their conditions.

Constraints

Constraints were used to enforce data integrity and maintain consistency within the database. For example, a NOT NULL constraint ensures mandatory data entry for specific columns.

By defining these elements, we create a robust and well-organized database schema in MySQL, ready to store and manage the data for the SafeSpace application.

Creating Sample Queries to Demonstrate Database Functionality

Once the schema was implemented, SQL was utilized to interact with the database. Sample queries were created from a list of possible user tasks to demonstrate the core functionalities the database offers. In order for these sample queries to work, fake data was generated with an LLM, specifically ChatGPT, and inserted into the database. The sample queries demonstrated my ability to do the following in MySQL:

Creating views specific to user needs
Inserting, updating, and deleting data in the database
Joining tables to retrieve related information
Creating views specific to user needs
Utilizing transactions for data consistency
Implementing triggers for automated actions
Running queries with subqueries for complex data retrieval

Developing and testing these sample queries validated the effectiveness of the database design and demonstrated its ability to support various tasks that are involved in the SafeSpace application.

Conclusion

This process ensured a well-structured relational database design that can efficiently manage the complex data needs of SafeSpace, safeguard its data integrity, while facilitating information access for different user groups.

Efficiency and Scalability

Normalization (3NF) minimizes data redundancy which leads to efficient data storage and retrieval. This is crucial for managing the potentially large volume of data expected within SafeSpace.

The use of associative entities allows for flexibility and future growth. As the application evolves, the database can accommodate new data types and functionalities without major restructuring.

Data Integrity and Consistency

This database ensures data integrity by adhering to normalization principles and utilizing primary and foreign keys, meaning data is accurate, consistent, and reliable. This is crucial for supporting evidence-based interventions and user trust.

Implementing constraints further safeguards data integrity by preventing invalid data entry and maintaining consistency across the database.

Accessibility for Diverse Users

The logical data model separates user data, practitioner data, and intervention data into distinct entities, facilitating targeted information access for different user groups. Patients can view their progress and manage their data, while practitioners can access relevant patient information and treatment plans.

The potential for creating user-specific views allows for customized interfaces that cater to the specific needs of each user group. For example, practitioners might have a dashboard view summarizing patient progress, while patients might see a view focusing on their own interventions and physiological data.

Overall, this well-structured relational database design serves as a strong foundation for SafeSpace, ultimately supporting the application’s goal of providing personalized interventions for PTSD management.