Rob the Fiddler

Getting mySql up and running

Getting the SQL up and running

Download

The backend will be mySql. Its free and widely used. You can download it from the website http://www.mysql.com/

Go to downloads and get the lasted version. For now just get the essentials.

Download and run the installer. Pretty much pick all the standard settings. The configuration wizard will ask for a root password. If you plan to make this public, pick a good password and save it.

We will have two users/passwords. The Root one you only need to set things up for now. The other user we will call “user” with the password of “user”.

After everything is installed, run a dos window.

> mysql -u root -p

[enter your password]

> grant all privileges on *.* to 'user'@'localhost' identified by 'user';

[so...great, now you have a user with a password of user (change if you want)]

exit and start mysql again from the prompt

>mysql -u user -p

[enter your password user]

create database workout;
use workout;

And there, we have a database up and running

Creating tables

We will start with some basic tables to play with. Each table will change over time as I get to it, so for now dont get too hung up on the contents, or visible passwords or whatever.

To start with, you need a user and some data.

CREATE TABLE person
(person_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
username VARCHAR(20),
fname VARCHAR(20),
lname VARCHAR(20),
gender ENUM('M','F'),
birth_date DATE,
address VARCHAR(40),
city VARCHAR(20),
state VARCHAR(20),
height SMALLINT UNSIGNED,
CONSTRAINT uc_person UNIQUE (person_id),
CONSTRAINT pk_person PRIMARY KEY (person_id)
);

Additionally, each user will add data. Lets start with a “Goal”. We could add this in the person table or elsewhere. This is where maybe we need to do some planning.

A quick diagram using UMLet is as follows.

The weight, measurements and such should be pretty easy. I suppose you could lump it all into one table or have different tables for each. In general, nobody enters all this stuff at the same time. Lets do them separate for now.

CREATE TABLE body_weight
( person_id SMALLINT UNSIGNED,
  entry_date DATE,
  weight SMALLINT UNSIGNED,
  CONSTRAINT pk_body_weight PRIMARY KEY (person_id, entry_date),
  CONSTRAINT fk_person_id FOREIGN KEY (person_id)
    REFERENCES person(person_id)
);

CREATE TABLE body_measurement
( person_id SMALLINT UNSIGNED,
  entry_date DATE,
  bicept SMALLINT UNSIGNED,
  chest SMALLINT UNSIGNED,
  abdomen SMALLINT UNSIGNED,
  waist SMALLINT UNSIGNED,
  thigh SMALLINT UNSIGNED,
  hip SMALLINT UNSIGNED,
  CONSTRAINT pk_body_measurement PRIMARY KEY (person_id, entry_date),
  CONSTRAINT  FOREIGN KEY (person_id)
    REFERENCES person(person_id)
);

CREATE TABLE body_weight_goal
( person_id SMALLINT UNSIGNED,
  entry_date DATE,
  weight SMALLINT UNSIGNED,
  CONSTRAINT pk_body_weight_goal PRIMARY KEY (person_id, entry_date),
  CONSTRAINT  FOREIGN KEY (person_id)
    REFERENCES person(person_id)
);

CREATE TABLE body_fat
( person_id SMALLINT UNSIGNED,
  entry_date DATE,
  weight SMALLINT UNSIGNED,
  CONSTRAINT pk_body_fat PRIMARY KEY (person_id, entry_date),
  CONSTRAINT   FOREIGN KEY (person_id)
    REFERENCES person(person_id)
);

CREATE TABLE body_pulse
( person_id SMALLINT UNSIGNED,
  entry_date DATE,
  resting_pulse SMALLINT UNSIGNED,
  CONSTRAINT pk_body_pulse PRIMARY KEY (person_id, entry_date),
  CONSTRAINT  FOREIGN KEY (person_id)
    REFERENCES person(person_id)
);

So did it work?

mysql> show tables;
+-------------------+
| Tables_in_workout |
+-------------------+
| body_fat          |
| body_measurement  |
| body_pulse        |
| body_weight       |
| body_weight_goal  |
| person            |
+-------------------+
6 rows in set (0.00 sec)

Ok, so thats good. Now the remaining tables are the hard part. The trick to relational databases is to remove as much redundant data as possible. You need to break down the data. For a Workout, you generally have a record which is a set of exercises which is a collection of sets (or laps…for now).

Here is my breakdown of the tables. The point of this exercise is not to explain entirely why I chose the following, so if your confused google “relational database normalization”. By breaking down the tables, you remove a lot of redunant entries. The queries get more complicated, but thats not really a problem for the database.

I also removed the notes from the workout, exercise records and exercise tables. Its pretty rare to have notes…so there is no point in every record having them.

The tables are pretty close to this…see real link below

CREATE TABLE workout
(
workout_id  INT UNSIGNED NOT NULL AUTO_INCREMENT,
person_id SMALLINT UNSIGNED,
entry_date DATE,
CONSTRAINT uc_workout UNIQUE (person_id,entry_date),
CONSTRAINT   PRIMARY KEY (workout_id),
CONSTRAINT  FOREIGN KEY (person_id)
REFERENCES person(person_id)
);

CREATE TABLE workout_note
( workout_id INT UNSIGNED,
notes VARCHAR(40),
CONSTRAINT pk_workout_note PRIMARY KEY (workout_id),
CONSTRAINT  FOREIGN KEY (workout_id)
REFERENCES workout(workout_id)
);

CREATE TABLE muscle_group
( group_id  SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
muscle_name VARCHAR(40),
CONSTRAINT pk_muscle_group_id PRIMARY KEY (group_id )
);

CREATE TABLE exercise
( exercise_id  INT UNSIGNED NOT NULL AUTO_INCREMENT,
person_id SMALLINT UNSIGNED,
/*  s = set and L = lap */
ex_type ENUM('S','L'),
primary_group SMALLINT UNSIGNED,
secondary_group SMALLINT UNSIGNED,
CONSTRAINT pk_exercise PRIMARY KEY (exercise_id),
CONSTRAINT  FOREIGN KEY (person_id)
REFERENCES person(person_id),
CONSTRAINT  FOREIGN KEY (primary_group)
REFERENCES muscle_group(group_id),
CONSTRAINT  FOREIGN KEY (secondary_group)
REFERENCES muscle_group(group_id)
);

CREATE TABLE exercise_note
( exercise_id INT UNSIGNED,
notes VARCHAR(40),
CONSTRAINT pk_exercise_note PRIMARY KEY (exercise_id),
CONSTRAINT  FOREIGN KEY (exercise_id)
REFERENCES exercise(exercise_id)
);

CREATE TABLE exercise_record
(
exercise_record_id  INT UNSIGNED NOT NULL AUTO_INCREMENT,
workout_id INT UNSIGNED,
exercise_id INT UNSIGNED,
CONSTRAINT pk_workout PRIMARY KEY (exercise_record_id),
CONSTRAINT  FOREIGN KEY (workout_id)
REFERENCES workout(workout_id),
CONSTRAINT  FOREIGN KEY (exercise_id)
REFERENCES exercise(exercise_id)
);

CREATE TABLE exercise_record_note
( exercise_record_id  INT UNSIGNED,
notes VARCHAR(40),
CONSTRAINT pk_exercise_record_note PRIMARY KEY (exercise_record_id),
CONSTRAINT  FOREIGN KEY (exercise_record_id)
REFERENCES exercise_record(exercise_record_id)
);

CREATE TABLE exercise_set
( exercise_record_id  INT UNSIGNED,
set_number SMALLINT UNSIGNED NOT NULL,
weight SMALLINT,
reps SMALLINT,
CONSTRAINT pk_set PRIMARY KEY (exercise_record_id, set_number),
CONSTRAINT  FOREIGN KEY (exercise_record_id)
REFERENCES exercise_record(exercise_record_id)
);

CREATE TABLE exercise_lap
( exercise_record_id  INT UNSIGNED,
lap_number SMALLINT UNSIGNED NOT NULL,
time INT,
distance INT,
CONSTRAINT pk_lap PRIMARY KEY (exercise_record_id, lap_number),
CONSTRAINT  FOREIGN KEY (exercise_record_id)
REFERENCES exercise_record(exercise_record_id)
);

The SQL to create and drop these tables are here:

Create_tables.sql

Drop_tables.sql

Getting mySql up and running

Getting the SQL up and running

Download

Creating tables

Rob Stave