1

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

  • Are the students supposed to compute the carbs, etc? Or do you intend to JOIN to another table and compute such in SQL? – Rick James Feb 1 at 21:57
  • In the user dashboard, I will display their total carbs, etc for the day with a query. – Tim M Feb 1 at 22:14
  • What is unit 180 if unit 147 is grams (and why is that on an inner object?). Besides that you can flatten the summary, nutritionValues and loggedFood objects into columns with a common prefix for grouping or completely let that detail fade. – eckes Feb 2 at 1:32
  • @eckes I'm not sure what 180 is or why an inner object. That's JSON supplied from the Fitbit API. Thanks. – Tim M Feb 3 at 2:49
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.