Designing a PostgreSQL Database

Thanks for joining me on this journey and reading my log for the day!

After almost two weeks of creating the front-end application, I’ve moved onto the database design!

How To Design a Relational Database

I’m not going to claim that this will be a full course on desiging relational databases, but I’ll walk you through the process that I just did today! If you’re interested in learning about more of the theory of relational databases, let me know and I’ll do another blog post on it sometime.

What Database Should I Use?

First, you need to determine if you even want to have a relational database. There are scenerios where it may be more beneficial for you to use a document-oriented database that will allow for you to change your fields and documents more easily over time.

A relational database, most likely using some flavor of SQL, is best for highly-structured data with many relationships that needs to be queried efficiently.

To be honest, I could have easily gotten away with using MongoDB for this project. But, I haven’t had the change to stand up a Postgres server in a production environment yet, so I wanted to make sure I learned how!

Deciding on Your Tables

I suggest that you do this on pen and paper. It’s much more frustrating to have to run the queries and edits that you need to make on an actual database, or even a database framing tool than it is on a piece of paper.

Take a look at the front end of your application and ask yourself, “What data needs stored here?”

And then, beyond that, you may want to ask yourself what data you, as the creator, want access to? If anyone needs to support the application, what data will they need access to?

Then begin splitting this information into tables. A good table can be filled with rows where each row represents one item and its relationships to other items.

For my application, I really only needed two tables: roasts and users . I could have broken it down more to avoid duplicate data by creating an origins and a varieties table, but, there really is no use for this for my purposes.

Create Your Columns

Each column here is a slice of data that belongs to a single record or row. So in a roast record, I have columns for date_roasted and starting_weight_g and a user_id to ensure that I can match the roasts to the correct user later.

When you’re creating your columns, the easiest thing to forget about is the relationships of the tables. Every relationship, (one-to-many, one-to-one, many-to-many) will need a column (and maybe even a separate table, to categorize the relationship.

I think it’s easiest to do this in a database framing tool like dbdiagram.io. You’ll use database markup to structure the tables, and it will quickly show you the relatinoships of the tables that you have on the screen. It helps me quickly identify the issues with my database design.

Here’s the schema of my tables taken from the DBML that I used on dbdiagram.

Table roasts {
  id integer [primary key]
  date_roasted date
  rating integer
  origin varchar
  variety varchar
  name varchar
  starting_weight_g float
  ending_weight_g float
  heat_level varchar
  start_temp_f integer
  lowest_temp_f integer
  first_crack_seconds integer
  temp_rise_seconds integer
  opened_lid_seconds integer
  heat_off_seconds integer
  dumped_seconds integer
  is_favorite boolean
  notes text
  user_id integer [Ref: > users.id]
}

Table users {
  id integer [primary key]
  first_name varchar
  last_name varchar
  email varchar
  username varchar
  password varchar
}

Create a Setup File For Your Database

Now, translate your tables into SQL so that you can run it with a shell script or import the file into a database manager! Trust me, doing it this way is much easier in the long run than querying each table, column, and constraint.

Here’s how my tables look:

CREATE TABLE "roasts" (
  "id" SERIAL PRIMARY KEY,
  "date_roasted" date,
  "rating" integer,
  "origin" varchar,
  "variety" varchar,
  "name" varchar,
  "starting_weight_g" float,
  "ending_weight_g" float,
  "heat_level" varchar,
  "start_temp_f" integer,
  "lowest_temp_f" integer,
  "first_crack_seconds" integer,
  "temp_rise_seconds" integer,
  "opened_lid_seconds" integer,
  "heat_off_seconds" integer,
  "dumped_seconds" integer,
  "is_favorite" boolean,
  "notes" text,
  "user_id" integer
);

-- Create the "users" table
CREATE TABLE "users" (
  "id" SERIAL PRIMARY KEY,
  "first_name" varchar,
  "last_name" varchar,
  "email" varchar UNIQUE,
  "username" varchar UNIQUE,
  "password" varchar
);

-- Add foreign key constraint to the "roasts" table
ALTER TABLE "roasts" ADD FOREIGN KEY ("user_id") REFERENCES "users" ("id");

Final Steps

After this, I usually make up some fake data to put in so that I have it to work with while I’m testing my API! A empty database is pretty useless.

There’s plenty more I didn’t get into here, like creating an executable script to stand up the database for you in different envrionments, setting up role based access within your database, and some more of the fundamentals of database design.

If you’re interested in diving deeper, here’s a few great articles to read:

What Is PostgreSQL?

Database | Relational Database | Codecademy

Database Normalization

Check Out the Project

If you want to keep up with the changes, fork and run locally, or even suggest code changes, here’s a link to the GitHub repo!

https://github.com/nmiller15/roast

The frontend application is currently deployed on Netlify! If you want to mess around with some features and see it in action, view it on a mobile device below.

https://knowyourhomeroast.netlify.app

Note: This deployment has no backend api, so accounts and roasts are not actually saved anywhere between sessions.