🍽️ Food.com Recipe Database — Database Design Project

A relational database design project built on the Food.com Recipes and Reviews dataset from Kaggle. The project covers the full pipeline from raw CSV data processing to a normalized MySQL database with queries, views, and stored procedures.

---

📌 Overview

Property	Details
Dataset	Food.com – Recipes and Reviews (Kaggle)
Recipes	522,517 recipes across 312 categories
Reviews	1,401,982 reviews from 271,907 users
Database	MySQL (relational, normalized)
License	CC0 Public Domain

---

🗂️ Project Structure

Database Design Project/
│
├── data_processing/           # Python scripts for preprocessing raw CSV files
│   ├── process_recipes.py
│   ├── process_reviews.py
│   ├── process_authors.py
│   ├── process_nutrition.py
│   ├── process_recipe_time.py
│   └── .env                   # File paths (not tracked by git)
│
├── Script/
│   └── script.sql             # Queries, views, and stored procedures
│
├── EER Diagram/
│   ├── EER Diagram.png        # Entity-Relationship diagram
│   └── group5_eerModel.mwb    # MySQL Workbench model file
│
├── Report/
│   └── Report.pdf             # Full project report
│
├── .gitignore
└── README.md

---

🧩 Database Schema

The database consists of the following tables:

Table	Key Columns
Authors	AuthorId, AuthorName
Recipes	RecipeId, Name, AuthorId, DatePublished, Description, RecipeCategory
Reviews	ReviewId, RecipeId, AuthorId, Rating, Review, DateSubmitted
Nutrition	RecipeId, Calories, FatContent, SaturatedFatContent, CholesterolContent, SodiumContent, CarbonhydrateContent, FiberContent, SugarContent, ProteinContent
Recipe_Time	RecipeId, cookTime, PrepTime, TotalTime

---

📊 EER Diagram

---

⚙️ Data Processing

Raw CSV files from Kaggle are large and contain many columns that are not needed for this project. The Python scripts in data_processing/ handle:

• Column filtering — Only the relevant columns are kept for each table
• Deduplication — Authors are extracted as unique entries from the reviews dataset
• Column renaming — e.g. CarbohydrateContent → CarbonhydrateContent, CookTime → cookTime
• In-place replacement — Processed files overwrite the originals to save disk space

Setup

1. Install dependencies:

   pip install pandas python-dotenv

2. Copy .env.example to .env and update file paths to match your local setup:

   RECIPES_INPUT_FILE=C:\path\to\your\recipes.csv
   RECIPES_OUTPUT_FILE=C:\path\to\your\recipes_temp.csv
   # ... (one entry per table)

3. Run each script individually:

   python data_processing/process_authors.py
   python data_processing/process_recipes.py
   python data_processing/process_reviews.py
   python data_processing/process_nutrition.py
   python data_processing/process_recipe_time.py

---

🗄️ SQL Script Contents

The Script/script.sql file includes:

Queries (12 total)

#	Description
1	Dessert recipes with fewer than 300 calories
2	Average calorie content grouped by recipe category
3	Authors ranked by total submitted recipes
4	High-rated recipes (rating ≥ 4) with their authors
5	High-protein recipes (protein > 40g)
6	Low-fat / diet-friendly recipes (fat < 5g)
7	Top 10 recipes with highest cholesterol
8	Recipes with carbohydrate content between 50–100g
9	Unreviewed recipes (LEFT JOIN + IS NULL)
10	Top 10 most reviewed recipes
11	Recipes published during COVID-19 lockdown (Spring 2020)
12	Top 20 quickest recipes by total time

Views (3 total)

View	Description
PopularRecipesReport	Recipe review counts and average ratings
DietAndHealthSummary	Macro nutrients per recipe
HighRiskNutritionalValues	Recipes exceeding sodium, sugar, or saturated fat thresholds

Stored Procedures (4 total)

Procedure	Description
sp_GetAuthorRecipes	All recipes by a given author ID, sorted chronologically
sp_GetRecipeNutritionalStatus	Classifies a recipe as "Diet Friendly" or "High Calorie"
sp_FindHealthierAlternative	Recommends a lower-calorie recipe in the same category
sp_ListMealsByMacros	Filters recipes by max carbs, max fat, and min protein

---

📁 Data Source

Dataset: Food.com – Recipes and Reviews Author: Alvin (Kaggle: irkaal) License: CC0: Public Domain

The raw CSV files are not included in this repository due to their large size (~758 MB compressed). Download them directly from Kaggle and configure your .env file accordingly.