Database Scheme

[j-nguyen]

Purpose

To be able to do CRUD operations on the calorie log tracker, weight log tracker and the weight scheme.

Proposed Database Format

CREATE TABLE time_log (
    id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    date DATETIME
);

CREATE TABLE weight_log (
    id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    time_id INTEGER REFERENCES time_log(id),
    weight FLOAT,
);

CREATE TABLE calorie_log (
    id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    time_id INTEGER REFERENCES time_log(id),
    food TEXT NOT NULL,
    calories INTEGER NOT NULL
);

CREATE TABLE progress ( 
    id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    resource TEXT NOT NULL
);

CREATE TABLE image_location (
    id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    image_id INTEGER REFERENCES progress(id),
    time_id INTEGER REFERENCES time_log(id)
);

Notes

With the way this db is structured, the time stamps are meant to record for each date. We can check if today's date has been recorded. If it's not recorded, then we create the date for today.

This is a proposal so I'm not sure if this is 100%

Update

More notes to account for:

• We will need to check if you've recorded a date today, however if you haven't then it's not a big deal. We can check using by if 24 hours have been passed. This should work for weight log, and for recording your calories, however, your weight log wouldn't make much sense, as it should be geared towards being weekly or monthly.

• If the weight log is to be weekly, we can still use the same database and reference the id's and such, just we have to make sure that it's been a week after we can record into calorie_log.

• I'm assuming to find the latest date, would to be get the last inserted id, or we can manually search for the latest date.

[j-nguyen]

@cfiliault Hey sir, I have a question in regards to creating a table. The weight log is supposed to be recorded weekly, so should I have a separate table for creating weight time log, and then another separate one for the calories time log.

So something like this?

CREATE TABLE calorie_log (
    id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    time_id INTEGER REFERENCES calorie_timelog(id),
    food TEXT
);

CREATE TABLE calorie_timelog (
    id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    date DATETIME
);

CREATE TABLE calorie_log (
    id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    time_id INTEGER REFERENCES calorie_timelog(id),
    food TEXT
);

CREATE TABLE calorie_timelog (
    id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    date DATETIME
);

Where calories are to be recorded daily, and weight is supposed to be recorded weekly.

[j-nguyen]

Wait this is the new updated one, I'm not sure anymore

[j-nguyen]

@cfiliault this is how my table looks so far. Calorie log is supposed to be recorded daily, where weight log table is recorded weekly. Is this db design right?

calorie_log table

id	time_id	food_id	date
PRIMARY KEY AUTOINCREMENT	FOREIGN KEY	FOREIGN KEY	DATETIME

weight_log table

id	weight	date
PRIMARY KEY AUTOINCREMENT	FOREIGN KEY	DATETIME

food table is from food class

id	name	blah	blah
PRIMARY KEY AUTOINCREMENT	blah	blah	blah

[j-nguyen]

Updated DB Format

This is almost official, but it's looking like this:

CREATE TABLE weight_log (
	id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
	weight FLOAT,
	DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE food (
	id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
	name TEXT,
	weight INTEGER,
	measure TEXT,
);

CREATE TABLE calorie_log (
	id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
	food_id INTEGER REFERENCES food(id),
	DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)l

CREATE TABLE progress (
	id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
	resource TEXT NOT NULL
);

CREATE TABLE image_location (
	id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
	image_id INTEGER REFERENCES progress(id),
	DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE workout (
	id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
	name VARCHAR(30),
	set INTEGER,
	rep INTEGER
);

CREATE TABLE workout_day (
	id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
	day VARCHAR(9)
);

CREATE TABLE workout_schedule (
	id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
	workout_id INTEGER REFERENCES workout(id),
	workout_day_id INTEGER REFERENCES workout_day(id)
);

[j-nguyen]

I'll have this done ASAP. This needs to be finished, before we can continue on with the others.