Prologue

Welcome to the PlatePal dev log number four! In this log, I will take you through the journey of exploring recipe schemas and the resulting implementation for my cookbook project. We will delve into the details of how I analyzed existing recipe schemas, considered various data structures, and implemented a relational schema in PostgreSQL. Creating relational databases is something I enjoy a lot - so much so I once created a game concept around it: https://docs.google.com/document/d/1pd48C9Zsd24BgM4iup6j-lOsTOnkYQFF7QtqPaCXaSU/edit?usp=sharing .

It goes without saying that this session was a lot of fun for me. Additionally, I was able to make even more enjoyable progress by receiving expert help from a friend in the UX department. This led to the first visualizations of where this project is headed in the form of wireframes.

Overview & General Progress (TLDR)

Tasks I worked on during this session:

  • ✅ Recipe database schema :
    • Research on recipe structure and existent data structures
    • Plan and implement schema in SQL
  • ✅ Wireframes :
    • Meeting with UX Designer Sabine to discuss planned features and workflow

During my research for the schema I also went through quite a few resources about recipe parsing. Since I want to implement this as well I saved some of those for the future:

Recipe database schema

As part of this project, it is my goal to develop a consistent data structure for recipes, which should allow the users to store each and every bit of information contained in their recipes. To get to this point I will probably need a lot of iterations, but for a start I decided to do some research on the topic. Once I had a first plan mapped out I created the database schema.

Research

My research efforts can be split into two parts:

  1. Which information needs to be stored

For this, I took a look at some sample recipes I picked out randomly from different sources, which quickly reminded me again in how many different ways a recipe can be described. What is prep time vs. active time vs. cook time vs. total time? How does “yields” differ from “makes” or “serves”? Do I want to allow the usage of multiple recipe lists for ingredients per recipe? In most cases I decided I’d rather store more than less information - and therefore differentiated where I could.

My notes on various recipes
My notes on various recipes

I also took a look at some existent recipe schemas, most importantly at schema.org/Recipe . This is the schema used by most commercial recipe websites and is therefore utilized by many (or probably even all) web recipe parsers. Once this project enters the web space, I want to make sure that I can easily fill in this schema with the data stored in the database.

  1. What do I want to use the information for?

This is mainly relevant to figure out how to split the information up. I could probably store a lot of information in json in the database, but that would make searching and for specific parts of this a lot more difficult, and not very performant.

For example, I knew I wanted to add a tagging system. Once the database gets filled up, it is not unlikely for it to contain millions of entries. Storing those tags in a json array would slow down searches in the database a lot, so I decided to create a recipe_tag table with an index on the tag name to allow for the fastest possible searches.

Another point I had to consider was the ingredient data structure. Ingredient lists in recipes can usually not be broken down easily, different ingredients contain all different kinds of extra information. Maybe a recipe requires not just “Cheese” - but a cheese with a very specific taste. Another recipe may require ingredients that have been processed beforehand. Extracting this information from already written recipes is not trivial, but some of the planned features in PlatePal require this data to be stored separately, specifically translation, search by ingredient, unit conversion and recipe time calculation. Therefore I had a look at existent recipe/ingredient parsers, which is a functionality I will integrate into PlatePal in the future as well - my favorite being this:

Turn Recipes into Data with Named Entity Recognition - Deepgram Blog ⚡️ | Deepgram

I decided to go with the structure they used in their model: An ingredient being described by

  • Food (ingredient name)
  • quantity
  • unit
  • physical quality
  • color
  • part
  • taste
  • process
  • purpose

Implementation

Once I had my plan laid out, I transferred everything into an actual relational schema in PostgreSQL. Thanks to my trusty helper tool DataGrip , I didn’t have to write all of the SQL by myself. Instead, I had a GUI to work with. With a schema of this size, it would otherwise be easy to get lost in the SQL file. Therefore, being able to constantly visualize the state of the schema was very helpful.

Having done this quite a few times now, I didn’t encounter too many issues. I spent some time creating and reworking the relationships in the schema. The collection_tag and recipe_tag tables used to have many-to-many relationships from collection/recipe to a tag table. Instead, I decided to create composite foreign keys for the first time. This was mainly because I wanted to avoid having two tables with two id columns each. Additionally, I wanted to see how this solution performs in the future and how I can later connect it to my Java application.

In case you haven’t worked with PostgreSQL before, you may also wonder about some of the data types used here, namely “interval” and “text”. In my opinion, PostgreSQL has quite a few helpful data types that are not included in other SQL database systems. For example, “text” is essentially the same as “varchar” (without length specification), but performs equally, if not better, in performance tests.

Visualization of the recipes and collections schema
Visualization of the recipes and collections schema

After implementing the whole schema in a temporary database, I used the SQL generator tool in DataGrip and created a liquibase migration in my backend project with the resulting code.

-- liquibase formatted sql

-- changeset pauline_roehr:20230911_1225_cookbook_schema.sql
CREATE SCHEMA platepal_recipes;
CREATE TYPE platepal_recipes.unit_type AS enum ('WEIGHT', 'VOLUME');

CREATE TABLE platepal_recipes.recipe
(
    recipe_id   bigserial
        CONSTRAINT recipe_pk
            PRIMARY KEY,
    title       text,
    yields      text,
    serves      integer,
    description text,
    cook_time   interval,
    prep_time   interval,
    active_time interval,
    total_time  interval,
    created_at  timestamp,
    created_by  bigint,
    updated_at  timestamp
);

COMMENT ON COLUMN platepal_recipes.recipe.created_by IS 'account_id';

CREATE INDEX recipe_title_index
    ON platepal_recipes.recipe (title);

CREATE TABLE platepal_recipes.unit
(
    unit_id      serial
        CONSTRAINT unit_pk
            PRIMARY KEY,
    unit_type    platepal_recipes.unit_type,
    fulltext     text,
    abbreviation text
);

CREATE TABLE platepal_recipes.image
(
    image_id bigserial
        CONSTRAINT image_pk
            PRIMARY KEY,
    path     text
);

CREATE TABLE platepal_recipes.ingredient
(
    ingredient_id    bigserial
        CONSTRAINT ingredient_pk
            PRIMARY KEY,
    ingredient_name  text,
    default_image_id bigint
        CONSTRAINT ingredient_image_image_id_fk
            REFERENCES platepal_recipes.image
            ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE INDEX ingredient_ingredient_name_index
    ON platepal_recipes.ingredient (ingredient_name);

CREATE TABLE platepal_recipes.recipe_image
(
    image_id  bigint NOT NULL
        CONSTRAINT recipe_image_image_image_id_fk
            REFERENCES platepal_recipes.image
            ON UPDATE CASCADE ON DELETE CASCADE,
    recipe_id bigint NOT NULL
        CONSTRAINT recipe_image_recipe_recipe_id_fk
            REFERENCES platepal_recipes.recipe
            ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT recipe_image_pk
        PRIMARY KEY (image_id, recipe_id)
);

CREATE INDEX recipe_image_recipe_id_index
    ON platepal_recipes.recipe_image (recipe_id);

CREATE TABLE platepal_recipes.recipe_ingredient_list
(
    list_id    bigserial
        CONSTRAINT recipe_ingredient_list_pk
            PRIMARY KEY,
    list_title text,
    recipe_id  bigint
        CONSTRAINT recipe_ingredient_list_recipe_recipe_id_fk
            REFERENCES platepal_recipes.recipe
            ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE INDEX recipe_ingredient_list_recipe_id_index
    ON platepal_recipes.recipe_ingredient_list (recipe_id);

CREATE TABLE platepal_recipes.recipe_ingredient
(
    ingredient_id      bigint NOT NULL
        CONSTRAINT recipe_ingredient_ingredient_ingredient_id_fk
            REFERENCES platepal_recipes.ingredient
            ON UPDATE CASCADE ON DELETE RESTRICT,
    ingredient_list_id bigint NOT NULL
        CONSTRAINT recipe_ingredient_recipe_ingredient_list_list_id_fk
            REFERENCES platepal_recipes.recipe_ingredient_list
            ON UPDATE CASCADE ON DELETE CASCADE,
    quantity           integer,
    unit_id            integer
        CONSTRAINT recipe_ingredient_unit_unit_id_fk
            REFERENCES platepal_recipes.unit
            ON UPDATE CASCADE ON DELETE RESTRICT,
    process            text,
    color              text,
    part               text,
    taste              text,
    purpose            text,
    CONSTRAINT recipe_ingredient_pk
        PRIMARY KEY (ingredient_id, ingredient_list_id)
);

CREATE TABLE platepal_recipes.recipe_step
(
    step_id     bigserial
        CONSTRAINT recipe_step_pk
            PRIMARY KEY,
    recipe_id   bigint
        CONSTRAINT recipe_step_recipe_recipe_id_fk
            REFERENCES platepal_recipes.recipe
            ON UPDATE CASCADE ON DELETE CASCADE,
    step_number integer,
    step_text   text,
    step_header text
);

CREATE INDEX recipe_step_recipe_id_index
    ON platepal_recipes.recipe_step (recipe_id);

CREATE TABLE platepal_recipes.recipe_note
(
    note_id   bigserial
        CONSTRAINT recipe_note_pk
            PRIMARY KEY,
    recipe_id bigint
        CONSTRAINT recipe_note_recipe_recipe_id_fk
            REFERENCES platepal_recipes.recipe
            ON UPDATE CASCADE ON DELETE CASCADE,
    note_text text
);

CREATE TABLE platepal_recipes.recipe_tag
(
    tag_title text   NOT NULL,
    recipe_id bigint NOT NULL
        CONSTRAINT recipe_tag_recipe_recipe_id_fk
            REFERENCES platepal_recipes.recipe
            ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT recipe_tag_pk
        PRIMARY KEY (recipe_id, tag_title)
);

CREATE INDEX recipe_tag_recipe_id_index
    ON platepal_recipes.recipe_tag (recipe_id);

CREATE INDEX recipe_tag_tag_title_index
    ON platepal_recipes.recipe_tag (tag_title);

CREATE SCHEMA platepal_collections;
CREATE TABLE platepal_collections.collection
(
    collection_id bigserial
        CONSTRAINT collection_pk
            PRIMARY KEY,
    created_by    bigint,
    title         text,
    created_at    timestamp,
    public        boolean,
    updated_at    timestamp,
    parent_id     integer
        CONSTRAINT collection_collection_collection_id_fk
            REFERENCES platepal_collections.collection
            ON UPDATE CASCADE ON DELETE SET NULL,
    position      integer
);

COMMENT ON COLUMN platepal_collections.collection.created_by IS 'account_id';

CREATE TABLE platepal_collections.collection_tag
(
    collection_id bigint NOT NULL
        CONSTRAINT collection_tag_collection_collection_id_fk
            REFERENCES platepal_collections.collection
            ON UPDATE CASCADE ON DELETE CASCADE,
    tag_title     text   NOT NULL,
    CONSTRAINT collection_tag_pk
        PRIMARY KEY (collection_id, tag_title)
);

CREATE INDEX collection_tag_collection_id_index
    ON platepal_collections.collection_tag (collection_id);

CREATE INDEX collection_tag_tag_title_index
    ON platepal_collections.collection_tag (tag_title);

CREATE TABLE platepal_collections.collection_recipe_entry
(
    collection_id bigint NOT NULL
        CONSTRAINT collection_recipe_entry_collection_collection_id_fk
            REFERENCES platepal_collections.collection
            ON UPDATE CASCADE ON DELETE CASCADE,
    recipe_id     bigint NOT NULL
        CONSTRAINT collection_recipe_entry_recipe_recipe_id_fk
            REFERENCES platepal_recipes.recipe
            ON UPDATE CASCADE ON DELETE CASCADE,
    position      integer,
    CONSTRAINT collection_recipe_entry_pk
        PRIMARY KEY (collection_id, recipe_id)
);

CREATE INDEX collection_recipe_entry_collection_id_index
    ON platepal_collections.collection_recipe_entry (collection_id);

CREATE INDEX collection_recipe_entry_recipe_id_index
    ON platepal_collections.collection_recipe_entry (recipe_id);

CREATE TABLE platepal_collections.account_collection
(
    account_id    bigint NOT NULL
        CONSTRAINT account_collection_account_account_id_fk
            REFERENCES platepal_accounts.account
            ON UPDATE CASCADE ON DELETE CASCADE,
    collection_id bigint NOT NULL
        CONSTRAINT account_collection_collection_collection_id_fk
            REFERENCES platepal_collections.collection
            ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT account_collection_pk
        PRIMARY KEY (collection_id, account_id)
);

CREATE INDEX account_collection_account_id_index
    ON platepal_collections.account_collection (account_id);

Wireframes

Even though I am currently working on an MVP, I still want to prioritize good usability and UX for the application. That’s why I sought help from an expert, my friend Sabine . She recently completed her Masters in User Experience and Interactive Media Design and offered to assist me with this project. During a brief meeting, I explained the relevant planned features to her, and we reviewed the user flow I had envisioned thus far. She set up a Miro board and created wireframes, which will greatly facilitate my work on the frontend and required REST API. It was very exciting to see how she made the first few elements of my plans come to life!

Open board in Miro ↗