javascript - 在 PostgreSQL 中将 JSON 格式转换为关系数据？

Question

我正在构建一个时间跟踪应用程序，用户可以在其中记录他们每天工作的时间。基本上，它是一个每天都有唯一输入字段的网格，如下所示:https://jsfiddle.net/L5u1fc0a/130/

因此，网格中每一行的数据结构都会返回这样的数据

{
    "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"
        }
      }
    ]
  },

这会将我的 PostgreSQL 数据库中的所有内容存储为 id“4”下的一行。我的问题是，有没有更好的方法来存储这些数据？我最好将其存储在关系表中，以便最终更容易求和和求平均值，但我不确定如何从客户端执行此操作

Answer 1

对事物进行关系建模就是采用现实世界中独立的意义单位或“事物”，并映射它们之间的关系。在这种情况下，您有:

1. 一个人

2. 一个人完成的一段工作

而这之间的关系是一个人可以有很多时期的工作。

所以你想要两个表:

1. 一个人员表，其中包含人员级别的所有数据的列，包括他们的名字、姓氏、可能的职位等。为了使事情变得简单，您可以为他们提供一个自动递增的主键 ID，因为您你的问题中有。

2. 工作日志表，包含 person_id、日期和小时数列。您实际上并不需要自动递增主键，因为可以依靠 person_id 和 date 的组合来唯一标识一行。

人员表应该已经存在并已填充 - Erik 是从该表中提取的。然后，您可以创建一个工作日志表，其外键为 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')
    })
});

我可能错过了一些东西，但你可以从上面得到总体思路。您当然需要首先在数据库中创建人员表和工作日志表。

另一种选择是根本不对数据进行关系建模，而是使用 MongoDB 或 DynamoDB 等内容按原样存储数据。您还可以研究使用像sequelize这样的ORM来使关系数据插入更容易，或者使用像Mongoose这样的东西来使MongoDB更容易。在建模、存储和检索数据方面确实有大量的选择。但是，如果您的首要任务是分析 - 进行求和和求平均值，那么关系存储可能是您的最佳选择。