A Data Model to Keep Track of Your Most Precious Possession

Being healthy and fit is a lifestyle, not a fad. People who realize the value of health make it a priority, keeping records of all their fitness-related facts. In this article, we’ll examine the design of the database behind a health and fitness application.

There are many applications which let users log their health and fitness information. A couple of big players like Apple, Google, and Microsoft have launched their own development APIs specifically for this market. For example, Google has ‘Fit’ and Microsoft has ‘HealthVault’.

In this article, I’ll explain the data model behind a health records application. First, let’s discuss exactly what we’d expect such an app to do.

Project Requirements for a Health Info App

Below are some features that a health info app should support:

• Users can create an account and store health information for multiple profiles, i.e. an individual can store health information for all their family members.
• Users can record their entire health history, including immunizations, past lab results, allergies, and family medical history.
• Users can store various measurements of health and fitness, like blood glucose (blood sugar) levels, blood pressure, body composition and dimensions including body mass index (BMI), cholesterol, height, weight, reproductive health, etc.
• Information can be recorded using various methods and units of measurement. As an example, blood glucose can be measured in mg/dL or mmol/L.
• There are no limits on how much information users can store.
• The system will also hold accepted health standards, such as blood pressure or BMI numbers, and will alert users if their numbers fall outside of “safe” or “normal” ranges.
• Users can also choose information (like blood glucose, height, weight, etc) to display on their personal dashboard. This way, they can monitor whatever they need to.

Rather than simply explaining what each section and table does in the data model, let’s answer some questions about it. The function of the various tables will become clear as we go along.

First, you can look at the complete data model if you’d like to.

The Data Model

Answering Questions About the Health Info Data Model

How Can Users Store Health Information for All Their Family Members Individually?

First, let’s talk about Account and Profile Management. This can be achieved by having two different tables; one (user_account ) for logging the details of people who register with the application, and one (user_profile) for logging details of all the different profiles a registered user creates. People can create a number of profiles – e.g. one for each of their family members.

Let’s look at the various tables that make this possible.

The user_account table holds basic details about the person registering with the application. Its columns are:

• id –A surrogate key column for this table that identifies each user uniquely.
• login_name – The name or other ID that the user chooses as their login name. A unique constraint must be imposed on this column to ensure that every login name is different.
• enc_password – The user-selected account password, in encrypted form.
• address columns – Stores address and contact details for users at the time of registration. These columns include street_address, city, state, country, and zip. Since these fields are optional in the registration process, I have kept these columns as nullable.
• contact_number and email – Stores the user’s contact number (i.e. phone number) and their email address. These fields are also part of the registration process, but they are not nullable.
• is_active – Holds either a ‘Y’ or an ‘N’ to indicate whether an account is currently active.
• account_image – Users are allowed to upload their own images. Since a user can upload zero or (max) one image per account, this is a nullable BLOB-type column.

The user_profile table stores details of all profiles created by registered users. The columns in this table are:

• id – A unique number assigned to each new profile.
• user_account_id – Signifies which user created the profile.
• user_profile_name – Stores the name of the person in the profile. (We will call this person the “profile person” and the user who creates the profiles the “account holder”.)
• relationship_id – Indicates the relationship between the account holder and the profile person. This column refers to the relationship table, which holds all possible types of relationships (like self, mother, father, sister, brother, son, daughter, pet, etc).
• email – This column holds the profile person’s email address. Reports or other information would be shared with them via this email; information would also be sent to the account holder. For example, if Melissa created a profile for her daughter Eva, Eva’s information would be sent to Melissa’s email and possibly to Eva’s email – see below.
• is_report_sharing_enabled – Reports are always shared with the account holder, but it is optional to share this data with the profile person. This column shows whether information will be shared with the profile person.
• is_active – Identifies whether a profile is currently active. This is a soft delete function in case profiles are accidentally deleted.
• profile_image – Stores an image of the profile person. This attribute is optional and thus nullable.

The characteristic_data table contains individual profile details (like blood group) that never change over time. All the columns in this table are self-explanatory except fitzpatrick_skin_type, which classifies the nature of one’s skin starting from I (always burns, never tans) to VI (never burns, no change in appearance when tan).

I have added two columns for gender; biological_gender signifies one’s gender at the time of birth, and current_gender signifies the profile person’s current gender. This second column is only applicable to transgender people, and therefore I have kept it nullable.

What Vital Information Can Be Stored in This System? How Is It Stored?

Now we’re moving on to Health Data Management. Body composition, blood glucose levels, and body dimensions are stored in separate tables. However people can enter more than one type of information at a time, so we use the body_vitals_log table to keep track of what information is logged into a profile and when it is entered.

All the vital stats are kept in the following tables:

• body_composition – Stores details about various body composition percentages like fat, lean mass, bone, or water. It also holds BMI (body mass index) values for individuals.
• blood_cholesterol – Holds cholesterol details like LDL, HDL, triglycerides, and total.
• body_dimension – Records the dimensions of various body areas, such as the measurements of the waist or chest.
• body_weight – Stores values for body weight.
• body_height – Holds values for a person’s height.
• blood_pressure – Holds blood pressure numbers (systolic and diastolic).
• blood_glucose – Records blood glucose levels.

Most of the columns in the above tables are self-explanatory, with a few exceptions. You’ll notice some additional columns like measurement_method_id, compare_to_normal_id, measurement_unit_id and measurement_context in almost every one of these tables. I’ll explain these columns later.

The body_vitals_log keeps track of what information is logged at a given time for a profile. The columns in this table are:

• user_profile_id – Shows which profile is logging the information.
• dt_created – Stores the date and time when the information is entered.
• data_source_id – Signifies the source of the data, such as a manual, an electronic device, etc.
• IDs of various vital statistics – I have kept all these columns nullable, as users are allowed to log one or more item at a time. Not all users will want to track the same health statistics.

How Can We Make the System Work in Different Regions?

Some information is measured in different units in various areas. For example, body weight is measured in kilograms in Asia, but it is measured in pounds in North America. So to make this workable in our database, we need a way to track measuring units.

• id – Serves as the primary key of this table, and it is the one which other tables refer to.
• measurement_parameter – Signifies the type of vital information (like weight, height, blood pressure, etc.) a unit measures.
• unit_name – Stores the name of the unit. Think of kilogram and pound for weight, mg/dL and mmol/L for blood glucose.

How Will People Know If Their Numbers Are Good?

Our system is not much help unless it alerts people to health risks or vulnerabilities. We enable this function by adding the comparison_to_normal_id column in all vital information data tables.

When any new vital information is logged into the system, records will be compared with their corresponding benchmark values and this column will be set accordingly.

Possible values for this table are:

I	Text
1	Don’t know
2	Much Lower
3	Lower
4	Normal
5	Higher
6	Much Higher

Can Users Record When Measurements Were Taken?

For example, users may need to state when their blood glucose was measured – i.e.before or after a meal. Or they may weigh themselves and record the results before and after exercising. To facilitate this, I have added a column, measurement_context, in the vital information tables that may need contextual information. Some possible values for this column are shown below:

Before Breakfast

After Breakfast

Before Lunch

After Lunch

Before Dinner

After Dinner

Before Exercise

After Exercise

Fasting

Non-Fasting

After Meal

Before Meal

Before Bedtime

What If a Person Is Diabetic and Needs to Monitor Their Blood Glucose Levels?

The system I am proposing will have a dashboard that can display vital stats in a graphical format. Users are allowed to choose what they would like to see on their profile dashboard, and each profile has its own dashboard. Account holders are allowed to see all the profile dashboards they have created.

I have added one CHAR(1) column for each parameter that can be shown on a dashboard. By default, all columns would be populated with ‘N’ (display is turned off) when a new profile is created. Users can later modify their dashboard configuration from an option in the app’s user interface.

How Does This System Help People Stay Fit?

In other words, we’re talking Fitness Data Storage. Besides health information, the system also allows their users to log information about their fitness and exercise routines.

The activity_log table is the main table in this subject area. It captures details about every sort of activity profile persons perform.

Every activity can be measured by one or more of the following three parameters:

• Start and end time – Activities like playing sports or games, standing in a queue, etc. are measured in terms of start and end time. This is done via the start_time and end_time columns in activity_log.
• Distance covered – Activities like running or cycling are measured in terms of distance covered. This is stored in the distance_covered column.
• Step count – Activities like walking are measured in terms of step counts, and the values are stored in the steps_count column.

You must be wondering why the calories_burnt column is in the activity_log table. As its name suggests, this column holds the value of calories burnt by the profile person while doing a particular activity. I’ll explain how we can compute these values in a later section.

I have created one table named activity to keep a list of all possible activities. The columns in this table are:

• id – Assigns a unique ID number to each activity.
• activity_name – Stores activity names.
• activity_multiplier – This column plays a key role in calculating the number of calories burnt by people pursuing activities.

How Do You Calculate the Calories Burned for Each Activity?

To understand how to calculate calorie burn, we first have to understand a person’s BMR, or basal metabolic rate. This tells us how many calories a body burns at rest. Each person’s BMR depends on their gender, age, weight, and height. From a data modeling perspective, a BMR is a slowly changing dimension, and as such it keeps changing with time. We will store the latest individual BMR values in the user_bmr table.

There are various methods used to calculate BMR values:

Method# 1: Harris-Benedict Method

BMR Men: 66 + (6.23 X weight in pounds) + (12.7 X height in inches) – (6.8 X age)

BMR Women: 655 + (4.35 X weight in pounds) + (4.7 X height in inches) – (4.7 X age)

Method# 2: Katch-McArdle Method

BMR (Men + Women): 370 + (21.6 * Lean Mass in kilogram)

Lean Mass = weight in kilogram – (weight in kilogram * body fat %)

We can use a person’s BMR and the activity multiplier mentioned above to find out how many calories a person burns when doing a given activity. The formula is:

Calories burnt = activity multiplier * BMR

Note: Both of the above BMR calculation methods use the same multiplier values for activities. For more information, see this article.

Can We Keep Profiles’ Historical BMR Values?

Yes. We can archive BMR values in the user_bmr_archive table.

We start by adding one column, id_version, to the existing user_bmr table. We keep increasing this value by 1 every time a profile person’s BMR value is updated.

The user_bmr_archive table is almost a replica of the user_bmr table. The only difference is that it has a dt_expired column instead of the dt_created and dt_modified columns. The dt_expired column stores the date when the version became invalid, i.e. when the BMR value is updated in user_bmr.

What If Users Want to Keep a Record of Their Immunizations, Family Medical History, and Allergies?

This system leverages the following tables to give users the ability to store additional health information.

The immunization table stores details about immunizations received by profile people. After the example, you’ll see a brief description of the columns this table contains:

Example – John Soo received the second of three doses of a Hepatitis B vaccine. It was administered by Dr. David Moore on 28th Nov 2016. The vaccination was given by an injection in the left hand. It is manufactured by Cipla (a pharmaceutical company).

• id – The primary key of this table
• user_profile_id – Refers to the user_profile_ID of John Soo
• vaccination_name – “Hepatitis B”
• dt_received – “28^th Nov 2016”
• number_in_sequence – “02”
• body_area_id – The ID of the left hand, referred from the body_area table
• provider_name – “Dr. David Moore”
• how_administered – “Injected” (other possible values include nasal spray, tablet, drops, syrup)
• manufacturer – “Cipla”

The allergy table stores details about any allergies experienced by profile persons. Below is the list of columns, with the relevant values given for each as per the example:

Example – Alison D’Souza experiences a cough when she eats yogurt. She first had this reaction when she was 8 years old. She consults Dr. Bill Smith, who prescribes some medicine and advises certain precautions. This allergy still persists, but its intensity is lower now.

• id – The primary key of the table
• user_profile_id – Refers to the user_profile_id of Alison D’Souza
• allergy_type_id – Refers to the ID for the ‘Food’ allergy type in the allergy_type table. (The allergy_type table defines various allergy types like food, medication, environmental, animal, plant, etc.)
• allergy_reaction_id – Refers to the ID of the ‘Cough’ allergy reaction in the allergy_reaction table.
• first_observed – The date when this reaction was first observed, i.e. when Alison was 8 years old.
• consulting_doctor_name – “Dr. Bill Smith”
• treatment_brief – A short description of the prescribed medicines and recommended precautions.
• does_treatment_cure_allergy – “Partially cured. Lowered intensity of reaction.”

The family_history table stores details about users’ medical family history. Again, we’ve listed the columns and the type of information that would be stored in them based on the following example.

Example – Diana’s mother Lisa has Parkinson’s disease (a neurological disorder). She has been undergoing treatment, but has gotten no tangible improvement.

• id – the primary key of the table
• user_profile_id – Diana’s user_profile_ID from the user_profile table
• Relationship_id – The ‘mother’ ID from the relationship table
• Relative_name – “Lisa”
• Date_of_birth – Lisa’s date of birth
• Date_of_death – NULL (Lisa is still alive, and fighting hard against the disease.)
• Condition_brief – A short description of how, when, and where the condition started, consultations, any relief, etc.
• Current_status – ‘Current’ (Other possible statuses are ‘Intermittent’ and ‘Past’.)
• How_it_ended – NULL

What Would You Add to This Data Model?

The system lets people know how many calories they burn while pursuing various activities, but it does not track how many calories they consume, or how nutritious their food choices are. Also, the system allows them to record their fitness data on a daily basis, but it does not let them set a goal, formulate a plan, and track their progress so they remain motivated.

Should we consider building these features into it? What changes need to be made to add these features?

Let us know your ideas!