boosten.org

Losing weight with MySQL

Since the birth of my daughters Dunja and Kyra I keep record of their weight and length. For that purpose I created a simple table in MySQL with four columns per row:

• name: either Dunja or Kyra
• date: when were weight and length measured
• weight: in grams
• length: in centimeters

From this information I created graphs (using gnuplot). Worked fine at the time. This is an example of such a graph (from Kyra in this case):

At some point in time my wife decided she had to lose some weight, and since I’m such a good husband, I wanted to help her with that by having the same diet (and I could lose some pounds too). Of course recording our weights was one of the objectives, so I started thinking about a new table structure.

Of course there wasn’t any sense in recording our lengths, since these don’t change that much anymore, but instead the percentage of body fat seemed a meaningful addition (our scale seems to be able to calculate this). Therefore I came up with the following structure:

Nothing spectacular, but I wanted to create some constraints: I wanted to prevent deleting of records, and I wanted to force the right names in the three child tables. The parent table Person only holds the names of the persons that are used in the child tables Weight, Length and Body Fat.

My table Person looks like this:

CREATE TABLE `Person` (
`name` varchar(15) NOT NULL,
PRIMARY KEY (`naam`)
) ENGINE=InnoDB

Remarks:

• Since name is going to be used as foreign key in the child tables, it has to be a primary key (making it unique and not null).
• To enforce constraints in MySQL, the engine needs to be InnoDB (instead of the default MyISAM)

select name from Person;
+--------+
| name   |
+--------+
| Dunja  |
| Jozina |
| Kyra   |
| Peter  |
+--------+
4 rows in set (0.00 sec)

The three child tables Weight, Length and Body Fat all look the same (except for the type field):

CREATE TABLE `Weight` (
`name` varchar(15) NOT NULL,
`date` date NOT NULL,
`weight` float NOT NULL,
PRIMARY KEY (`name`,`date`),
CONSTRAINT `weight_ibfk_1` FOREIGN KEY (`name`) REFERENCES `Person` (`name`)
) ENGINE=InnoDB

Remarks:

• The primary key for this table is the combination of name and date, since both values separately can exist more than once (all four persons in the Person table could enter their weight at the same date, and obviously each person can add more than once his or her weight, but not on the same day).
• The constraint between Weight and Person is of a ‘ON DELETE RESTRICT’, ‘ON UPDATE RESTRICT” type. This is not shown, but implicit by design. That means that a row in the Person table not can be deleted or changed whenever records exist in the child tables Weigth, Length or Body Fat that reference that row in the Person table.
• The constraint also ensures that no name can be entered in the child tables, if it doesn’t exist in the parent table Person.
• I could have combined the three child tables into one, and specify the type (weight, length, body fat) in an additional column, but then some other things had to be arranged, like another table to create constraints (to make sure only these three values would be entered), and I would have to extend the primary key. It’s a design thing, I guess.

To prove the above delete constraint:

delete from Person where name='Jozina';
ERROR 1451 (23000): Cannot delete or update a parent row:
a foreign key constraint fails (`Weight`.`BodyFat`,
CONSTRAINT `Bodyfat_ibfk_1` FOREIGN KEY (`name`) REFERENCES `Person` (`name`))

This error message states that both the Weight and the Body Fat table hold records for Person Jozina. Therefore deleting Jozina from Person is not an option.

Creating graphs from this data is an entire different story. I use gnuplot for this, since I think it’s a great tool. First I needed to create a query, that produces the information gnuplot needs. I will describe only the Weight/Body Fat combination, but the Weight/Length combination (for my daughters) is the same, but used different tables.

From a shell prompt I type this, and redirect the output to a file that is used for gnuplot:

mysql -NBe 'SELECT a.date, a.weight , b.bodyfat \
FROM Weight AS a LEFT JOIN BodyFat AS b \
ON a.date=b.date AND a.name=b.name \
WHERE a.name="Peter" ORDER BY a.date' > weight.dat

Remarks:

• I chose the easy way for creating aliases (a and b), but you might want to use something more descriptive when creating such a query.
• I LEFT JOINed BodyFat to Weight. This ensures that all values of Weight are used. The problem with this query however is that Body Fat entries that have no corresponding Weight entry (matching date) will not show in this query. Again a design decision, since weight and body fat are displayed simultaniously by our scale.

The following script is used to actually create an image (png in my case):

set term png transparent nocrop enhanced font arial 8 size 800,600
set output "/home/peter/images/Peter.png"
set size 1, 1
set origin 0, 0.0
set grid xtics ytics mytics
set bmargin 10
set tmargin 2
set xtics rotate nomirror auto
set ytics nomirror
set mytics 2
set y2tics nomirror
set yrange [60:92]
set y2range [10:40]
set title "Peter's weight"
set ylabel "weight"
set y2label "Body Fat"
set xdata time
set timefmt "%Y-%m-%d"
set format x "%d-%m-%y"
plot "weight.dat" using 1:2 title "weight" with lines lt 1 lw 2, \
"weight.dat" using 1:3 axes x1y2 title "% fat" with lines lw 2 lt 2

This entry was posted on Friday, June 5th, 2009 at 9:07 pm and is filed under MySQL. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.