Food Data Table Relationships

[kellyjonbrazil]

I am using the Full Download of All Data Types from FoodData Central for my app:https://fdc.nal.usda.gov/download-datasets.html

(I see a new version was just posted, so I'll have to check that out)

Is there a document that shows all of the table relationships so I can model them in my app? I don't have MS Access so I'm using the CSV's and importing into an SQLite database.

Thanks!

[hphungnal]

@kellyjonbrazil Unfortunately, we do not have a document with the table relationships available to distribute (FDC has transitioned to using a graph database). There is a download fields description PDF that should have been part of the .zip file that should hopefully help. You can also get that here: https://fdc.nal.usda.gov/portal-data/external/dataDictionary (this link can be found on https://fdc.nal.usda.gov/download-datasets.html).

[kellyjonbrazil]

In case this helps anyone, here are the simple table relationships in the sqlite schema:

CREATE TABLE food (
    fdc_id INTEGER NOT NULL, 
    data_type VARCHAR, 
    description VARCHAR, 
    food_category_id VARCHAR, 
    publication_date VARCHAR, 
    PRIMARY KEY (fdc_id)
);
CREATE TABLE branded_food (
    fdc_id INTEGER NOT NULL, 
    brand_owner VARCHAR, 
    gtin_upc VARCHAR, 
    ingredients VARCHAR, 
    serving_size VARCHAR, 
    serving_size_unit VARCHAR, 
    household_serving_fulltext VARCHAR, 
    branded_food_category VARCHAR, 
    data_source VARCHAR, 
    modified_date VARCHAR, 
    available_date VARCHAR, 
    market_country VARCHAR, 
    discontinued_date VARCHAR, 
    PRIMARY KEY (fdc_id), 
    FOREIGN KEY(fdc_id) REFERENCES food (fdc_id)
);
CREATE TABLE food_nutrient (
    id INTEGER NOT NULL, 
    fdc_id INTEGER, 
    nutrient_id INTEGER, 
    amount FLOAT, 
    data_points INTEGER, 
    derivation_id INTEGER, 
    min FLOAT, 
    max FLOAT, 
    median FLOAT, 
    footnote VARCHAR, 
    min_year_acquired VARCHAR, 
    PRIMARY KEY (id), 
    FOREIGN KEY(fdc_id) REFERENCES food (fdc_id)
);
CREATE TABLE nutrient (
    id INTEGER NOT NULL, 
    name VARCHAR, 
    unit_name VARCHAR, 
    nutrient_nbr FLOAT, 
    rank INTEGER, 
    PRIMARY KEY (id), 
    FOREIGN KEY(id) REFERENCES food_nutrient (nutrient_id)
);

[littlebunch]

In the same spirit as @kellyjonbrazil, here are schema for mariadb and postgresql used by a utility I developed for loading the Branded Food Products dataset. A mysqldump of the latest database built from the October csv is available at https://go.littlebunch.com. There's a graphql demo which uses the MariaDb database at rs.littlebunch.com.

I also developed a utility for loading the datasets into NoSQL stores. There's a REST API demo using Couchbase for BFPD at https://go.littlebunch.com/doc/.

Props as always to USDA for making this data publicly available!