helixml / demo-recipes

Recipe Database Outline

This outline describes the structure and implementation of the SQLite database (recipes.sqlite) used to store recipe data.

Database Schema

The database consists of three tables:

  • recipes: Stores basic information about each recipe.
  • ingredients: Stores information about ingredients used in recipes.
  • recipe_ingredients: Links recipes to ingredients.

Recipes Table

Column Name Data Type Description
id INTEGER Primary key, unique identifier for each recipe
name TEXT Name of the recipe
description TEXT Brief description of the recipe
instructions TEXT Detailed instructions for preparing the recipe
image_url TEXT URL of an image associated with the recipe
prep_time INTEGER Estimated preparation time in minutes
cook_time INTEGER Estimated cooking time in minutes
servings INTEGER Number of servings the recipe yields
category TEXT Category of the recipe (e.g., “Breakfast”, “Dinner”, “Dessert”)

Example:

INSERT INTO recipes (name, description, instructions, image_url, prep_time, cook_time, servings, category)
          VALUES (
            'Spaghetti with Tomato Sauce',
            'A classic Italian pasta dish.',
            'Cook spaghetti according to package instructions. Heat tomato sauce in a pan and toss with spaghetti.',
            'https://example.com/spaghetti-image.jpg',
            10,
            20,
            4,
            'Dinner'
          );

Ingredients Table

Column Name Data Type Description
id INTEGER Primary key, unique identifier for each ingredient
name TEXT Name of the ingredient
quantity REAL Quantity of the ingredient used in a recipe
unit TEXT Unit of measurement for the ingredient (e.g., “grams”, “cups”, “teaspoons”)

Example:

INSERT INTO ingredients (name, quantity, unit)
          VALUES (
            'Spaghetti',
            500,
            'grams'
          );

Recipe Ingredients Table

Column Name Data Type Description
recipe_id INTEGER Foreign key referencing the recipes table
ingredient_id INTEGER Foreign key referencing the ingredients table

Example:

INSERT INTO recipe_ingredients (recipe_id, ingredient_id)
          VALUES (
            1,
            1
          );

This link connects the recipe for “Spaghetti with Tomato Sauce” (recipe_id 1) to the ingredient “Spaghetti” (ingredient_id 1).

Queries

You can use SQL queries to retrieve recipe data based on your needs.

Example:

To retrieve all recipes in the “Dinner” category:

SELECT * FROM recipes WHERE category = 'Dinner';

To retrieve a recipe with a specific name:

SELECT * FROM recipes WHERE name = 'Spaghetti with Tomato Sauce';

To retrieve all ingredients used in a specific recipe:

SELECT i.name, i.quantity, i.unit
          FROM ingredients i
          JOIN recipe_ingredients ri ON i.id = ri.ingredient_id
          JOIN recipes r ON ri.recipe_id = r.id
          WHERE r.name = 'Spaghetti with Tomato Sauce';

Database Maintenance

It’s essential to keep the database up-to-date and organized.

  • Data Integrity: Ensure that all data is accurate and consistent.
  • Regular Backups: Create regular backups of the database to prevent data loss.
  • Optimization: Optimize the database structure and indexes for efficient data retrieval.