Converting JSON format to relational data in PostgreSQL?

Question

I'm building a time-tracker app where users can log how many hours per day they are working. Basically, it's a grid with a unique input field for each day, something like this: https://jsfiddle.net/L5u1fc0a/130/

So, the data structure for each row in the grid returns data like this

{
    "id": 7,
    "firstname": "Joe",
    "lastname": "Bergen",
    "email": "joe@gmail.com",
    "hour": [
      {
        "id": 4,
        "user_id": 7,
        "userinput": {
          "2018-11-10": "8",
          "2018-11-11": 10,
          "2018-11-12": "7",
          "2018-11-13": "8",
          "2018-11-14": "8",
          "2018-11-15": "10"
        }
      }
    ]
  },

This will store everything in my PostgreSQL database as one row under the id "4". My question is, is there a better way of storing this data? I'd preferably have it stored in a relational table so it's easier to do sums and averages eventually, but I'm not sure how to do it from the client side

Answer 1

Modelling things relationally is all about taking real world self-contained units of meaning or 'things' and mapping the relationship between each of those. In this case you have:

1. A person

2. A period of work completed by a person

And the relationship between these is that one person can have many periods of work.

So you want two tables:

1. A person table with columns for all data at a person level, their first name, last name, potentially job title etc. And to make things easy you can give them an auto-incrementing primary key id, as you have in your question.

2. A work log table, with columns person_id, date, and hours. You don't really need an auto-incrementing primary key, as the combination of person_id and date can be relied to uniquely identify a row.

The person table should already exist and be populated - where Erik is being drawn from. Then you can create a worklog table with a foreign key of person_id that references the person table. So in a typical application it would work like this:

In client-side a day's work is logged for a particular person. The application sends an http POST request to the backend server with this data:

fetch("/worklog/new", {
    headers: {
      'Accept': 'application/json',
      'Content-Type': 'application/json'
    },
    method: "POST",
    body: JSON.stringify({
      person_id: 1,
      date: '2018-11-15'
      hours: 8
   })
})

In your backend server:

app.post('/worklog/new', (req, res) => {
  const { person_id, date, hours } = req.body

  const { Pool } = require('pg');
  const pool = new Pool({
    connectionString: process.env.DATABASE_URL
  });
  pool.query(`
    INSERT INTO worklog (person_id, date, hours)
    VALUES ($1, $2, $3)
  `, [person_id, date, hours]).
    .then(() => {
      pool.end();
      res.send('WORKLOG ADDITION SUCCESFUL')
    })
    .catch(err => {
      pool.end();
      console.error(err)
      res.status(500).send('WORKLOG ADDITION FAILED')
    })
});

I might have missed something but you can get the general idea from above. You of course need to first create the person table and the worklog table in your database.

The alternative would be to not model the data relationally at all, and instead use something like MongoDB or DynamoDB to store the data as is. You could also investigate using an ORM like sequelize for making relational data inserts easier, or something like Mongoose to make MongoDB easier. There really are an overwhelming number of options when it comes to modelling, storing, and retrieving data. However if your priority is analysis - doing sums and averages, then relational storage is probably your best choice.