# Database Design ## Introduction A database is a systematically organised collection of data stored and designed in a way that makes it easy to access, manage, and analyse information. The Smart Health database will act as a foundationary repository for research data from which researchers can request data extracts for cardiovascular research projects. Any bona fide researcher will be able to apply to access the linked smartphone, wearable, and NHS datasets. This will facilitate research seeking to predict, prevent, diagnose and monitor any human disease. The processing pipeline produces a range of daily summary metrics for physical activity, sleep, heart rate, and environmental exposures. These research-ready datasets will be linked to NHS data. ```{note} The MVP focuses on developing the pipeline that generates the daily summary metrics from Fitbit devices and GPS data. ``` --- ## Entity-relationship Diagram An entity relationship diagram (ERD) is a high-level visual representation of the data model of the database. It describes the relationships between core objects and concepts within a system. Below is the ERD for the database developed in the initial MVP phase. ```{figure} ../_static/sh_mvp_erd.webp :align: centre :alt: The entity-relationship diagram shows that each unique "Sheffield ID" is linked with 0 or more data entries in the Fitbit Daily Summary and Intraday activies tables, or the Intraday LSOA, Mobility, or Environmental data tables An ERD showing the relationships between the database tables containing Fitbit, processed GPS, and environmental data. ``` Each study participant is assigned a unique `sheffield_id`. If they have consented to sharing their Fitbit data, this `sheffield_id` will be associated with their 'Daily Summary' and 'Intraday Activities' data in a one-to-many relationship - one participant can have many (or none) records in these data tables. Similarly, if a participant has consented to sharing their GPS data, they can have many records of their intraday Lower Super Output Area (LSOA), mobility, and environmental data. The data tables are associated with a participant by a composite primary key consisting of their `sheffield_id` and a date or timestamp that identifies each record. ```{tip} A __primary key__ is a unique identifier for a record in a table. A primary key can be a __composite key__ which consists of two or more columns in a database table that uniquely identify a row. ``` --- ## Data Dictionary (MVP only) Click the toggle button below to see the data dictionary of fields generated by the MVP pipeline. This dictionary includes information about the field name, it's device or reference data source (e.g. Fitbit or smartphone GPS, or external reference data), whether the field is from raw data or derived, a short description, and the metric units (where applicable). Note that the units will remain constant as UK metric units. Daiser will be overriding a participants' default units setting to unify the data as UK metric before data transfer. The [Fitbit API documentation](https://dev.fitbit.com/build/reference/web-api/developer-guide/application-design/#Units) outlines the default UK metric units. Please also refer to the [Fitbit Web API Data Dictionary](https://assets.ctfassets.net/0ltkef2fmze1/45IN5bvBS827grKEsA8ZB0/648f3778acc936961f0572590c005ef0/Fitbit-Web-API-Data-Dictionary-Downloadable-Version-2.pdf) for more information. Note that the `AHAH` Data Source refers to the Access to [Healthy Assets & Hazards (AHAH)](https://data.geods.ac.uk/dataset/access-to-healthy-assets-hazards-ahah) dataset[^sn1]. The reference data used is __Version 4__ (released 2024). [^sn1]: The data for this research have been provided by the Geographic Data Service ([geods.ac.uk](geods.ac.uk)), a Smart Data Research UK Investment: ES/Z504464/1. ```{eval-rst} :download:`Download the data dictionary here <../_static/data_dict_mvp.csv>` ``` ```{toggle} ```{csv-table} :file: ../_static/data_dict_mvp.csv :header-rows: 1 :widths: auto ``` --- ## Proposed tables (post-MVP) After the initial MVP phase, development will continue to incorporate data streams from Baseline Questionnaires and Health App data, alongside linking the clean data to NHS records. --- ## Database architecture The Smart Health database has a data lake architecture, using Apache Parquet file formats stored in Amazon Web Services (AWS) Simple Storage Solution (S3) object storage. This object storage is often referred to as a "bucket". S3 storage is cost-effective and highly scalable. It can also be scaled independently from the compute resources as needed. The Smart Health database is entirely handled within a highly access-restricted [RONIN](https://ronin.cloud/) cloud-computing environment. This design is a highly-secured, cost-effective, and performance-optimised analytical data store. It prioritises security and compliance through the RONIN Secure Data Environment (SDE) while using an industry-standard data lake architecture to enable fast, scalable analysis. ##### Data Format Data is stored as Apache Parquet files within the S3 buckets. ```{epigraph} Apache Parquet is an open source, column-oriented data file format designed for efficient data storage and retrieval. It provides high performance compression and encoding schemes to handle complex data in bulk and is supported in many programming languages and analytics tools. -- [parquet.apache.org](parquet.apache.org) ``` In summary, this format is fast to query, cost-effective, and avoids vendor lock-in. Parquet files also contain schema metadata, enabling query engines to understand the data structure on the fly in a schema-on-read data processing approach. ##### Directory Structure The Parquet files are structured within file directories analagous to a typical file explorer system. As the expected data volume is large, each table will be divided into smaller chunks in a process called __partitioning__. Each dataset will be partitioned by date. This is discussed in detail below. Overall, the directory structure containg the files is as follows: ```{note} :class: margin This structure outline corresponds to the MVP data only ``` ```{code-block} shell ├── clean_bucket │   └── year │   └── month │   └── day │   ├── clean_environmental_YYYY-MM-DD.parquet │   ├── clean_fitbit_intraday_YYYY-MM-DD.parquet │   ├── clean_fitbit_summary_YYYY-MM-DD.parquet │   ├── clean_lsoa_YYYY-MM-DD.parquet │   └── clean_mobility_YYYY-MM-DD.parquet ``` ##### Data Partitioning Data partitioning is an optimisation techique used in data lakes. Large datasets are physically divided into smaller, more managable sub-datasets using one or more specified columns. Typically, the columns used are frequently queried. For example, in the Smart Health database, we partition by __date__ as the dataset is updated on a daily basis. By partitioning the data, a query engine can read only the files needed for a particular query. This reduces the amount of data scanned and lowers query cost whilst improving performance. ### Querying the database The Data Connect team will use DuckDB[^sn2] to query the Smart Health data lake. [^sn2]: [DuckDB](duckdb.org) is a simple, light-weight, and open-source database system. There are a number of benefits of using this tool, including: * DuckDB runs in-process, meaning no dedicated server infrastructure is needed to host the database; instead, the user's virtual machine resources are used. * DuckDB can read parquet files directly from the S3 bucket and leverage the data partitioning to only query the necessary data. * As a columnar database, it is suited to analytical queries and makes use of the benefits of Parquet file formats * DuckDB offers a host of advance SQL features referred to as "friendly SQL", making queries easier and faster to write and execute. Multiple data scientists will be able to concurrently read from the Smart Health database to create research data extracts. ```{figure} ../_static/query_database.webp :align: center :width: 600px :alt: Multiple virtual machines within the RONIN Isolate environment can query the data in the Smart Health clean database concurrently. After data extraction, anonymisation, and output checking, the data will be egressed to researchers' RONIN Core environment for analysis The Smart Health database can be simultaneously queried by multiple virtual machines within the RONIN Isolate environment. After egress checking, fully pseudonymised data extracts from the Smart Health database can be transferred to researchers' Secure Data Environments in RONIN Core ```