🗄️ JSON to Database Schema Mapper
Mapping the raw USDA record to the intelligent_foods_usda_brands MySQL table.
| JSON Field | Example Value (Ranch Dip) | Current DB Mapping | Missing / Action Required |
|---|---|---|---|
| 1. Core Identity & Metadata | |||
| gtinUpc | “071100213413” | ✔ barcode | Perfect fit. |
| description | “RANCH-DIPPED PIZZA ORIGINAL…” | ✔ product_name ✔ foodname_sanitised |
Mapped. (You split this perfectly in your importer). |
| brandOwner | “HIDDEN VALLEY” | ✔ original_source | Mapped. |
| brandedFoodCategory | “Dips & Salsa” | ✔ category | Mapped. |
| dataType / foodClass | “Branded” | ✔ primary_type | Mapped. |
| fdcId | 1121709 | Stored in JSON | ⚠️ NEW: fdc_id (INT) Highly recommended to store the official USDA ID outside the JSON for API updates. |
| 2. Physical Properties & Text | |||
| ingredients | “VEGETABLE OIL, WATER, TOMATO…” | ✔ ingredients_text | Mapped. |
| servingSize | 30 | Stored in JSON | ⚠️ NEW: serving_text (VARCHAR) We should combine these three fields into one clean string: “30ml (2 Tbsp)”. |
| servingSizeUnit | “ml” | Stored in JSON | |
| householdServingFullText | “2 Tbsp” | Stored in JSON | |
| 3. Administrative Dates & Meta | |||
| modifiedDate | “9/15/2020” | Stored in JSON | ⚠️ NEW: modified_date (VARCHAR) |
| availableDate | “9/15/2020” | Stored in JSON | ⚠️ NEW: available_date (VARCHAR) |
| publicationDate | “11/13/2020” | Stored in JSON | ⚠️ NEW: publication_date (VARCHAR) |
| marketCountry | “United States” | Stored in JSON | Leave in JSON (Unless you plan to expand internationally). |
| dataSource | “LI” | Stored in JSON | ⚠️ NEW: data_source (VARCHAR) |
| 4. labelNutrients (Macros Per Serving) | |||
| calories | 110 | ✔ energy | Mapped. (You already have an ‘energy’ DECIMAL column!) |
| protein | 0.000 | Stored in JSON | ⚠️ NEW: protein (DECIMAL 10,2) |
| carbohydrates | 2.00 | Stored in JSON | ⚠️ NEW: carbs (DECIMAL 10,2) |
| fat | 12.0 | Stored in JSON | ⚠️ NEW: fat (DECIMAL 10,2) |
| sugars / addedSugar / fiber / sodium / calcium… | … | Stored in JSON | Leave in JSON payload. Only promote to DB columns if your UI needs to filter/sort by them. |
| 5. foodNutrients Collection (Scientific Per 100g) | |||
| foodNutrients | { “name”: “Energy”, “amount”: 367 }, { “name”: “Protein”, “amount”: 0.00 }… | ✔ food_data_json | Keep safely inside the food_data_json longtext column. Extract via PHP only when clinical 100g math is needed. |
| 6. Extraneous Collections (Logs & Tags) | |||
| foodUpdateLog | {“modifiedDate”: “5/28/2020”}, {“modifiedDate”: “12/6/2019”} | ✔ food_data_json | These are bulky nested lists. Flat SQL tables cannot store lists natively. Keeping them in the food_data_json column is the correct architectural choice! |
| foodAttributes | {“name”: “Description”, “value”: “2”} | ✔ food_data_json | |
| tradeChannels / microbes | “NO_TRADE_CHANNEL” / empty | ✔ food_data_json | |