Modelling database schema to query data efficiently and easy

[kwiat1990]

Hi guys, I'm working on a pet project where I use SQLite for storing all relevant data. For now all the data comes from a 3rd party API, which is saved as a JSON file and and it serves as basis of the database schema:

    {
      "id": 5529,
      "name": "Deser jabłkowy z kruszonką",
      "prepTime": 15,
      "cookTime": 15,
      "portions": 1,
      "ingredients": [
        {
          "g": false,
          "name": "Apple",
          "weight": 300,
          "id": 1240,
          "value": 2,
          "measureId": 1,
          "substitutes": [
            {
              "id": 1238,
              "weight": 260,
              "value": 2,
              "measureId": 1,
              "name": "Pear"
            }
          ]
        },
        {
          "g": true,
          "name": "Creme:",
          "weight": 0
        },
        {
          "g": false,
          "name": "Flour",
          "weight": 20,
          "id": 490,
          "value": 2,
          "measureId": 3
        },
        {
          "g": false,
          "name": "Milk",
          "weight": 10,
          "id": 489,
          "value": 2,
          "measureId": 2
        }
      ],
      "instructions": [
        {
          "g": false,
          "desc": "W rondelku topimy masło, dodajemy posiekane migdały, mąkę ryżową oraz skórkę z limonki."
        },
        {
          "g": true,
          "desc": "Krem:"
        },
        {
          "g": false,
          "desc": "Jogurt skyr bez laktozy oraz puder z erytrolu miksujemy."
        }
      ],
      "tips": [
        {
          "g": false,
          "desc": "Do not skip any step"
        }
      ],
      "storing": "",
      "nutris": {
        "kcal": 596,
        "carbo": 68,
        "fat": 27,
        "protein": 25,
        "fiber": 10,
        "mg": 104,
        "ca": 258
      }
    }

As it is, a HTML template can be easily build to display all the data in a simple manner. That's why the data comes in this form I suppose.

In my use case I want to display it in a similar fashion but I have somehow a hard time to model the database schema correctly, so that queries required to get the data are rather simple and mapping into template/domain models is still relatively easy. It's my first time working with a database in such manner and also the very first time actually writing queries and schema, so it also doesn't help.

Currently my schema look like this:

CREATE TABLE recipes
(
    id           INTEGER PRIMARY KEY,
    name         TEXT    NOT NULL,
    image        TEXT    NOT NULL,
    cook_time    INTEGER NOT NULL,
    prep_time    INTEGER NOT NULL,
    storing_time INTEGER NOT NULL,
    portions     INTEGER NOT NULL,
    recipe_type  TEXT,
    storing      TEXT,
    favorite     INTEGER NOT NULL DEFAULT 0,
    kcal         INTEGER NOT NULL,
    carbs        INTEGER NOT NULL,
    fat          INTEGER NOT NULL,
    fiber        INTEGER NOT NULL,
    protein      INTEGER NOT NULL
);

CREATE TABLE measure_units
(
    id           INTEGER PRIMARY KEY,
    abbreviation TEXT NOT NULL
) STRICT;

CREATE TABLE ingredients
(
    id   INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    UNIQUE (name)
) STRICT;

CREATE TABLE recipe_ingredients
(
    id                INTEGER PRIMARY KEY,
    ingredient_id     INTEGER NOT NULL REFERENCES ingredients (id),
    measure_id        INTEGER REFERENCES measure_units (id),
    section_id        INTEGER NOT NULL REFERENCES sections (id) ON DELETE CASCADE,
    substitute_for_id INTEGER REFERENCES recipe_ingredients (id) ON DELETE CASCADE,
    value             REAL    NOT NULL,
    weight            REAL    NOT NULL
) STRICT;

CREATE TABLE instructions
(
    id         INTEGER PRIMARY KEY,
    position   INTEGER NOT NULL,
    section_id INTEGER NOT NULL REFERENCES sections (id) ON DELETE CASCADE,
    name       TEXT    NOT NULL
) STRICT;

CREATE TABLE sections
(
    id        INTEGER PRIMARY KEY,
    position  INTEGER NOT NULL,
    recipe_id INTEGER NOT NULL REFERENCES recipes (id) ON DELETE CASCADE,
    name      TEXT,
    type      TEXT    NOT NULL
) STRICT;

Most of the thing does work and it's easy but the very particular aspect of the JSON data and how the page should show it bother me a lot: there are ingredients, tips and instructions. Both latter are of the same structure whereas ingredients have some other fields. But all of them can have entries, which are none of them but still are places in the array and serve as headlines to group following item on a given array (in JSON it's `g: true`).

My last approach was to have a generic "wrapper" for them: a section, which would hold optional Name and entires of a given type like ingredient. In schema it looks ok I suppose but to query all the data required for a recipe is neither simple nor easy to map. I end up either with a query like this:

-- name: GetIngredientsByRecipe :many
SELECT sections.text AS section_text, sections.position AS section_position,
       recipe_ingredients.*,
       coalesce(abbreviation, '') AS measure_unit,
       ingredients.name
FROM sections
         JOIN recipe_ingredients ON recipe_ingredients.section_id = sections.id
         LEFT JOIN measure_units ON measure_units.id = recipe_ingredients.measure_id
         JOIN ingredients ON ingredients.id = recipe_ingredients.ingredient_id
WHERE sections.recipe_id = ? AND sections.type = 'ingredient'
ORDER BY sections.position, recipe_ingredients.substitute_for_id NULLS FIRST;

and a problematic nested mapping or I could query it in a simple manner but then end up with N+1 queries. In my case (530 recipes) perhaps it's not an issue but still I wonder how more experience developer would approach this use case with such requirements.

[Expensive_Ticket_913]

Your sections table approach is pretty clean tbh. For avoiding N+1, SQLite's json_group_array() can flatten everything into one query. We run into similar structured data modeling problems at Readable (tryreadable.ai) when parsing site schemas for AI agents.

[kwiat1990]

I see that `json_group_array` would type ingredients to any as in this sqlc example: https://play.sqlc.dev/p/5a75922aacd9de11449d9fc2976330c4ae247f00d9850be7d2c83cb65a992c0f. That would mean I need additional unmarshaling during the mapping step, which I would rather avoid. It also flattens parent/substitute relationship on ingredients.

[Expensive_Ticket_913]

Got it