Should I extract the "time" part of "timestamp" into "json" data type?

Question

I am designing a database(postgresql) that can store a user's meals across the day, and there'll be weekly, monthly, or yearly analysis based these everyday information in the future. My original design was to have a meals table that looks like:

meals (table name)

• id (primary key)
• user_id (foreign key)
• picture_ids
• labels
• note
• timestamp

With this structure, querying data of a user's meals in a specified day may first filter meals by user_id, then by timestamp. And for one user, there will be multiple records inserted for multiple meals during one day.

Another way is use [json or jsonb type](https://www.postgresql.org/docs/current/datatype-json.html) to extract the "time" part from `timestamp` so the `timestamp` only contains 'year', 'month', 'date', thus I may have a table named `meals_of_day`:

meals_of_day (table name)

• id (primary key)
• user_id (foreign key)
• records (json type) <----------
• date

And data in the records column may look like:

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

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

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

This way, one user only has one row for each day, insertion of a meal is actually an updating of the records column of the row corresponding to user+date.

---

1. Which approach will have a shorter response time when a user log in a meal?
2. If the analytical feature involves a lot of time related queries, which approach would be more efficient?

Thanks.

Answer 1

Of the two approaches (and knowing nothing about your application layer, front-end architecture, etc) I'd prefer the meals table approach. It's simple, fairly flexible (seems to make few assumptions about how the data will be used), prevents possible update-conflicts on the jSONB structure, and is fairly simple to migrate away from (to the mails_per_day approach or something else) should the need arise.