Database Scheme

[j-nguyen]

Database Scheme

[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.