Mysql – Optimal schema for the JSON data

database-designMySQL

I am requiring my students to keep a 3 day food log. I want to use that data to display on their personalize web dashboard and will be bringing that info in as a JSON. The primary data I'll display is the date, food they ate, calories from fat, protein, carbs and total calories.

What would be the best way to structure my MYSQL database? Should I have one row in the same table with all the data? In the example below, the student drank chocolate milk. Would I have one row with fields for student ID, name of food, fat, protein, carbs and date? They may drink chocolate milk more than once per day so that would be a lot of duplicate information in each row but I'm not sure how to better structure this?

Below is a sample of the JSON I'll be inserting into my table(s):

{
    "foods":[
        {
            "isFavorite":true,
            "logDate":"2011-06-29",
            "logId":1820,
            "loggedFood":{
                "accessLevel":"PUBLIC",
                "amount":132.57,
                "brand":"",
                "calories":752,
                "foodId":18828,
                "mealTypeId":4,
                "locale":"en_US",
                "name":"Chocolate, Milk",
                "unit":{
                    "id":147,
                    "name":"gram",
                    "plural":"grams"
                },
                "units":[226,180,147,389]
            },
            "nutritionalValues":{
                "calories":752,
                "carbs":66.5,
                "fat":49,
                "fiber":0.5,
                "protein":12.5,
                "sodium":186
            }
        }
    ],
    "summary":{
        "calories":752,
        "carbs":66.5,
        "fat":49,
        "fiber":0.5,
        "protein":12.5,
        "sodium":186,
        "water":0
    },
    "goals":{
        "calories": 2286
    }
}

Thanks in advance,

Tim

Best Answer

Really what I do typically is just bring in all the data I think I'll ever need into the SQL database. Maximize ease of import and future flexibility.

If you don't want to show "milk" twice --- deal with that downstream. A secondary table or view that has these calculations or additional logic applied.

Typically with JSON-->SQL, the only major problem is that JSON can contain dynamic fields, whereas SQL tables have fixed fields.

It doesn't appear that your JSON contains much dynamic structure anyway (a field like tags, or other stuff like that).

So you really you can just do a 1:1 translation of all the JSON fields you care about into SQL columns, and make one row per entry. I would do this route personally -- you can call it the staging table. Apply calculation or visual logic afterward into a separate table, view, or logic.

If there are dynamic fields, that only pop up in relatively unstructured ways on certain items, create a field or two that simply contains a full brain-dump of these fields and deal with them later in SQL as well. That's been my experience with JSON to SQL.

Related Question