我应该将“时间戳”中的“时间”部分提取为"json“数据类型吗？

Question

我正在设计一个数据库(postgresql)，它可以在一天内存储用户的膳食，将来还会根据这些日常信息进行每周、每月或每年的分析。我最初的设计是有一个meals表，它看起来像：

meals (表名)

• id (主键)
• user_id (外键)
• picture_ids
• 标签
• 备注
• 时间戳

使用这种结构，在指定的一天中查询用户膳食的数据首先可以通过meals过滤user_id，然后通过timestamp过滤。对于一个用户，将有多个记录插入多餐在一天。

​

另一种方法是使用json或jsonb型从timestamp中提取“时间”部分，这样timestamp只包含‘年份’、‘月’、‘日期’，因此我可能有一个名为meals\_of\_day的表：

meals_of_day (表名)

• id (主键)
• user_id (外键)
• 记录(json类型)<？
• 日期

records列中的数据可能如下所示：

{
  "08:30": {
    picture_ids: [1,2],
    labels: ['meat', 'apple'],
    note: 'meat was too salty'
  },

  "12:45": {
    // some data
  },

  "19:05": {
    // some data
  }
}

这样，每天只有一个用户只有一行，插入一顿饭实际上是对行的records列的更新，该列对应于user+date。

​

1. 当用户登录一顿饭时，哪种方法的响应时间会更短？
2. 如果分析功能涉及大量与时间相关的查询，那么哪种方法会更有效？

谢谢。

Answer 1

在这两种方法中(而且对您的应用层、前端架构等一无所知)，我更喜欢meals表方法。它很简单，相当灵活(似乎对数据的使用方式做了很少的假设)，防止了jSONB结构上可能的更新冲突，并且在需要时很容易从(迁移到mails_per_day方法或其他方法)迁移。