0

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.

Xullnn
  • 405
  • 3
  • 17
  • I would consider the response time difference to be premature optimization. Even if one is 50% faster... you're probably talking sub-millisecond differences. There are many other considerations (like how these approaches impact your application layer, front-end, etc) I would consider, but not the difference in write performance. – melcher Aug 27 '21 at 17:55
  • What are the kind of time related queries that you want to do analytics on? You can index on keys inside of a JSONB, so it's possible to make queries against nested JSON data quite performant, but it depends greatly on what you're trying to do. – melcher Aug 27 '21 at 17:57

1 Answers1

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.

melcher
  • 1,543
  • 9
  • 15
  • Thanks @melcher. Your comments and answer did mention me some concerns I haven't considered. The `meals_of_day` approach reduces some complexity on queries but it loses some flexibility. And using `json` or `jsonb` needs [more effort to implement constrains](https://www.postgresql.org/docs/current/functions-json.html) upon that column. The frontend is still on prototyping phase, I build [a low fidelity prototype on figma](https://www.figma.com/proto/cpPIbnwWO6BBONS1IBYcbd/v1.0?node-id=122%3A389&scaling=scale-down&page-id=111%3A387&starting-point-node-id=235%3A437) if you were interested. – Xullnn Aug 28 '21 at 03:35
  • There is another table named `states` which takes inputs from users about their feelings at a certain moment. `states` and `meals` will be shown on the same timeline to provide possible insights about the relationship between their diet habit and their states. And this `timeline + meals + states` graph can be viewed in different scales(day, week, month etc.). The main data for each `meal` are photos a user takes, and my original thought was to implement a foreign key column of `array` type (see `picture_ids` in the question description) in the `meals` table to reference photos data. – Xullnn Aug 28 '21 at 03:48
  • Arrays of foreign keys are not supported (yet) in Postgres. See https://stackoverflow.com/questions/41054507/postgresql-array-of-elements-that-each-are-a-foreign-key – melcher Sep 02 '21 at 14:51
  • 1
    Instead you can have a meal_id foreign key on the pictures table or use a join table (e.g. “meals_pictures” with a picture_id and a meal_id) – melcher Sep 02 '21 at 16:14
  • I considered both join table and array of foreign keys. I thought I might use an array to hold all the ids of pictures for a meal, then use an inclusion check to find all corresponding pictures -- something like: `select * from pictures where id = ANY(picture_ids_of_a_meal);` [mentioned here](https://stackoverflow.com/questions/39643454/postgres-check-if-array-field-contains-value). But as the answer says this can't keep referential integrity, So I think **a join table is more viable.** Thanks @melcher : ) – Xullnn Sep 03 '21 at 14:42