Преобразование формата JSON в реляционные данные в PostgreSQL?

Question

Я создаю приложение для отслеживания времени, в котором пользователи могут регистрировать, сколько часов в день они работают. По сути, это сетка с уникальным полем ввода для каждого дня, примерно так: https://jsfiddle.net/L5u1fc0a/130/

Итак, структура данных для каждой строки в сетке возвращает данные, подобные этой

{
    "id": 7,
    "firstname": "Joe",
    "lastname": "Bergen",
    "email": "[e-mail]",
    "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"
        }
      }
    ]
  },

Это сохранит все в моей базе данных PostgreSQL как одну строку с идентификатором "4". У меня вопрос, есть ли лучший способ хранения этих данных? Я бы предпочел, чтобы он хранился в реляционной таблице, чтобы в итоге было проще делать суммы и средние значения, но я не уверен, как это сделать со стороны клиента.

Answer 1

Реляционное моделирование вещей - это все, что нужно для принятия в реальном мире автономных единиц значения или "вещей" и картирования отношений между каждым из них. В этом случае у вас есть:

1. Персона

2. Период работы, выполненный человеком

И отношения между ними таковы, что один человек может иметь много периодов работы.

Итак, вы хотите две таблицы:

1. Таблица персонала со столбцами для всех данных на уровне человека, их имени, фамилии, потенциально должности и т. Д. И чтобы упростить задачу, вы можете дать им автоматически увеличивающийся идентификатор первичного ключа, как у вас в вопросе.

2. Таблица журнала работ, с колонками person_id, датой и часами. Вам на самом деле не нужен автоинкрементный первичный ключ, поскольку можно использовать комбинацию person_id и date, чтобы однозначно идентифицировать строку.

Таблица персонажа должна уже существовать и быть заполненной - откуда взялся Эрик. Затем вы можете создать таблицу рабочего журнала с внешним ключом person_id, который ссылается на таблицу person. Так что в типичном приложении это будет работать так:

На стороне клиента ведется дневная работа для конкретного человека. Приложение отправляет запрос http POST на внутренний сервер со следующими данными:

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
   })
})

На вашем бэкэнд-сервере:

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')
    })
});

Возможно, я что-то пропустил, но вы можете получить общее представление сверху. Вам, конечно, нужно сначала создать таблицу person и таблицу worklog в вашей базе данных.

Альтернативой было бы вообще не моделировать данные, а вместо этого использовать что-то вроде MongoDB или DynamoDB для хранения данных как есть. Вы также можете исследовать использование ORM, например sequelize, для облегчения вставки реляционных данных, или что-то вроде Mongoose, чтобы упростить MongoDB. Существует действительно огромное количество вариантов, когда речь заходит о моделировании, хранении и извлечении данных. Однако, если вашим приоритетом является анализ - выполнение сумм и средних значений, то реляционное хранилище, вероятно, является вашим лучшим выбором.