Translate

Sunday, April 5, 2015

Monitoring health data - an improvement

The purpose of the article is to start a discussion  about a "system" capable to store and retrieve/analyze health data.
More articles will follow on specific issues.  This article is the continuation of the previous articles "Monitoring health data - my way #1 and #2".

The use of the spreadsheet as main tool to collect data for now is preserved, because it is  easy to store the information in that way, but retrieve them in a meaningful way can become tricky.
This why I started to think a better way to do this "daily chore", in a way that will easily allow me to create better reports and analyze the data more deeply, creating also reports "on the fly".

The system


The system is based on a database that collect all the information and a set of macro/applications/programs to facilitate the data entry and the retrieval.
The database is based on mySQL.
Macro in the spreadhseet (Libreoffice Calc) allows to export the data stored in the SQL tables.

The database


The database is based on some tables that collects the measurements and other related values.
At the moment I created 4 tables:

  • Measurement table
  • Measurement type table
  • Food table
  • Food type table

Measurement tables

T_MES


These are the main tables used to store the health measurements.
The main table (T_MES) contains the actual readings, and the T_MES_TYPE contains specific information about the type of measurement.
Each measure is stamped with date and time, however some measurements will have a fake time.
For example the weight. Usually there is an entry per day, so the time is forced at 8:00 am regardless when actually was taken, because not always I can measure at the same time.

Let see in details the fields of T_MES:

  • mesIndex
    Unique ID for the record, it is a MD5 Hash of the date, time and value measured
  • dateMeasure
    It is the date of the measurement
  • timeMeasure
    It is the time of the measurement. Weight and Ketons have a fake time entry. 8:00 am for Weight, 9:00 am for Ketons
  • typeMeasure
    Foreign key for the table T_MES_TYPE
  • noteMeasure
    Contains specific measurement note. The nature of the note depends about the measurement.
  • valueMeasure
    Value of the measurement
The T_MES_TYPE table contains information about the measure.
  • typeMeasure
    Unique ID for the record. Incremental number
  • description
    A brief description of the measurement. Explains what is measured
  • units
    Measurement unit of the measurement

Here the data to load in the T_MES_TYPE table :

typeMeasure code unitsdescription 
1ml/dg Glycemy
2lb  Weight 
countCarbs 
4mmHgBlood pressure systolic 
mmHg Blood pressure diastolic
bpmHeart beat (beat per minute)
 7count
Ketons 
 8minutes
Exercise elliptical 
 9miles 
Exercise walking
 10miles 
Exercise hiking

Here the mySQL commands to create the two tables :

create table if not exists t_mes (mesIndex varchar(40) not null primary key, dateMeasure date, timeMeasure time, typeMeasure numeric, valueMeasure numeric);

create table if not exists t_mes_type (typeMeasure numeric not null primary key, description varchar(20), unit varchar(10));

T_FOOD


The T_FOOD tables (T_FOOD and T_FOOD_TYPE) stores information about the food eaten.

Let see in details the fields of T_FOOD:

  • foodIndex
    Unique ID for the record, it is a MD5 Hash of the date, time and value measured
  • dateMeal
    It is the date of the meal
  • typeMeal
    It is the time of the meal according to a fixed schedule.
    It is a fixed value like this :
    • Breakfast
    • Lunch
    • Dinner
    • Snack
  • foodType
    Foreign key for the table T_FOOD_TYPE
  • serving
    Number of serving consumed
The T_FOOD_TYPE table contains information about the food.
  • foodType
    Unique ID for the record. Hash of the food name, brand and carbs
  • name
    Name of the food
  • servings
    Number of servings in a product (if applicable)
  • servingUnits
    Measurement unit used for the servings
  • carbs 
    Net Carbs in grams contained in 1 serving
  • calories
    Calories contained in 1 serving
  • protein
    Proteins in grams contained in 1 serving
  • totalFat
    Total fat in grams contained in 1 serving
  • sodium
    Salt in milligrams contained in 1 serving
  • potassium
    Potassium in milligrams contained in 1 serving
  • brand
    Name/description brand of the food. For vegetables/fruit use the word "raw".
  • notes
    Notes about the food
Here the mySQL commands to create the two tables :

create table if not exists t_food (foodIndex varchar(40) not null primary key, dateMeal Date, typeMeal varchar(30), foodType varchar(40), serving Numeric);


create table if not exists t_food_type( foodType varchar(40) primary key not null, name varchar(40), servings Float, servingUnits varchar(15), carbs Float, calories Float, protein Float, totalFat Float, sodium Float, potassium Float, brand varchar(40), notes varchar(40));

No comments:

Post a Comment