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	units	description
1	ml/dg	Glycemy
2	lb	Weight
3	count	Carbs
4	mmHg	Blood pressure systolic
5	mmHg	Blood pressure diastolic
6	bpm	Heart beat (beat per minute)
7	count	Ketons
8	minutes	Exercise elliptical
9	miles	Exercise walking
10	miles	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));