In a previous post, I detailed the steps I took to create an application, crafted lovingly with Python, which allowed a user to log in, authenticated that user, and allowed a user to set goals and to track their activities leading to achieve those goals.

One of the challenges that I ran into with building that is that the data wasn’t persistent; once the application was shut down, all of the values )(stored in a key-value dictionary) disappeared, and the program, once started back up again, was reset as it were to factory settings. What was needed was a database, which is what we’ll be building today.

To start, I planned out what I wanted the app to do, giving it more thought now that I was going to have a database attached. I created entities with ascribed attributes attached. Here’s my ER diagram:

Now for implementation. Here’s my SQL statements to build out the tables and attributes, let’s start with the users table:

CREATE DATABASE babysteps; # create DB

# Create users table with attributes
CREATE TABLE users (
	id SERIAL,
	username TEXT UNIQUE NOT NULL,
	first_name TEXT NOT NULL,
	last_name TEXT NOT NULL,
	email TEXT NOT NULL,
	phone_number INTEGER,
	password TEXT NOT NULL,
	PRIMARY KEY(ID)
);

A user will have a unique username and password, giving first and last name and things like a phone number and email. The latter is for future functionality- sending email and SMT text reminders, for example. A primary key is used to make each row unique- this id column will be referenced by other tables.

Next is the activities table. Two metric fields will be provided to allow the user to track whatever they need to track. Three options will be available for them to note how they are feeling about the day’s activity :), :/, or :(.

Here’s the statement:

CREATE TABLE activities (
	id SERIAL,
	date DATE,
	notes TEXT,
	title VARCHAR(30),
	feeling TEXT,
	metric_one TEXT,
	metric_two TEXT,
	CONSTRAINT feeling_check CHECK (feeling IN ('sad face', 'staring blankly', 'happy face'))
);

Now, let’s create the goals table. The user can set the desired goal, a set date to achieve it by and the image will potentially hold s3 endpoint string data pointing to an image file in AWS. Here’s the statement:

CREATE TABLE goals (
	id SERIAL,
	name TEXT NOT NULL,
	description TEXT,
	date DATE,
	image TEXT,
	PRIMARY KEY(id)
);

The last entity is categories, which a goal will attach to. For example, the goal of becoming stronger will fit under the Fitness category. Here’s the SQL statement:

CREATE TABLE categories (
	id SERIAL,
	name TEXT NOT NULL UNIQUE,
	description TEXT
);

There will be many users and many activities, so it will be a many-to-many relationship. We have to build a linking table that links the two tables together:

CREATE TABLE users_activities (
	user_id INTEGER NOT NULL,
	activity_id INTEGER NOT NULL,
	PRIMARY KEY(user_id,activity_id)
);

I think that’s actually not right- I believe we only need to have a foreign key in the activities table that references the primary key in users. Here’s the code for that:

ALTER TABLE activities
ADD COLUMN user_id INTEGER NOT NULL;

We need to add a constraint now to build that in, setting the foreign key:

ALTER TABLE activities 
ADD CONSTRAINT fk_activities_users
FOREIGN KEY (user_id)
REFERENCES users (id);

We need to do something similar between goals and categories table. Let’s get goals table ready for accepting the categories id as a foreign key:

ALTER TABLE goals
ADD COLUMN categories_id INTEGER NOT NULL;

The tricky part, for me, after having made all my tables, was understanding how to implement foreign keys and constraints. My understanding is this: for a many-to-many relationship, you have to create an intermediary table. Then, in that table, you hold foreign keys for the two corresponding tables. Now, for each table, you have to add a foreign key column for the table that is “connected” to it.