LAB 7 : Food Database refactoring for MVP

Demo Data Strategy

To ensure our Milestone 2 demo features scientifically accurate and realistic content, we are transitioning to a standards-based strategy using the USDA Food and Nutrient Database for Dietary Studies (FNDDS).

1. Data Source Transition

We will improve our Local Bulk Seeding Strategy using the USDA FNDDS 2021-2023 dataset.

• Why FNDDS: Unlike standard reference lists that often contain raw agricultural commodities, FNDDS is specifically designed for dietary studies. It includes comprehensive data on mixed dishes and prepared foods (e.g., "Lasagna", "Whole Milk"), making it more applicable to real-world user meal planning.
• Offline Capability: By parsing and seeding this data locally, we eliminate latency and rate-limiting risks during the demo, ensuring instant retrieval of complex nutritional profiles.

2. Database Schema Evolution

To handle the complexity of nutritional data efficiently, we are refactoring our backend data model from a monolithic structure to a normalized Many-to-Many relational architecture.

• Food Entity Table: This table will act as the primary catalog, storing high-level metadata such as the FDC ID (USDA identifier), food name/description, and category. It remains lightweight to ensure fast search performance.

• Nutrient Entity Table: We are creating a separate, distinct table for Nutrients (e.g., "Calcium", "Iron", "Vitamin C"). This normalization prevents "wide" database tables and allows us to dynamically add or track new types of nutrients without altering the database schema structure.

• Many-to-Many Relationship (The Associative Entity): The core of our nutrition logic lies in the Many-to-Many association between Food and Nutrient. This relationship is defined by an associative table (join table) that stores the specific data points required for calculation. This relation contains two critical fields:

  1. Nutrient Quantity: The actual amount of the specific nutrient present (e.g., 125 for Calcium).
  2. Food Portion: The reference basis or serving size information associated with that quantity (e.g., standardizing to 100g or a specific serving unit), allowing the application to calculate totals based on user consumption.

Micronutrient Expansion: This table structure will allow us to expand beyond our previous MVP macronutrients (Protein, Fat, Carbs) to include essential micronutrients such as Calcium (mg), Iron (mg), Vitamin C (mg), and Sodium (mg), enabling the health-focused features of our application.