PostgreSQL schema structuring

Question

This might be too broad a question but I'm building a blog app (Express/React/Postgres) and I'm new to the backend world.

I have tables for users and posts and if I want to track user reactions (thumbsup, smile, laugh, etc.) would you do that in the posts table or create a new reactions table with fkey references to users.id and posts.id?

I ask because I did the latter, then realized that posts can have multiple users and reactions, so I'm not sure the right / best way to do that, even with JOIN.

If it were JSON, this is how I see it in my mind, but I don't know how to translate this to SQL tables and knex queries.

[
  {
    "posts": [
      {
        "post_id": "1234567890ABCDEF",
        "content": "foo bar",
        "created_at": "01/18/2020",
        "reactions": {
          "users": [
            {
              "user_id": "D237NH23DGU6",
              "reaction": "thumbsup"
            },
            {
              "user_id": "DVSY8N8VSDYN6",
              "reaction": "angry"
            },
            {
              "user_id": "3D2J892D3J8933",
              "reaction": "laughing"
            },
            {
              "user_id": "23987DNHIUO8DD",
              "reaction": "thumbsup"
            }
          ]
        }
      },
      {
        "post_id": "99FJ48FJ4F8JHSH",
        "content":  "bar foo",
        "created_at":  "01/18/2020",
        "reactions": {
          "users": [
            {
              "user_id": "DSDF879344FH97",
              "reaction": "sad"
            },
            {
              "user_id": "8S7DF62HF87H34F",
              "reaction": "peace"
            },
            {
              "user_id": "37HHF783H4K47834",
              "reaction": "laughing"
            }
          ]
        }
      },
      {
        "post_id": "1234567890ABCDEF",
        "content":  "foo foo bar bar",
        "created_at":  "01/18/2020",
        "reactions": {
          "users": [
            {
              "user_id": "SDFWEF934949J4",
              "reaction": "thumbsup"
            },
            {
              "user_id": "R9TVU8TRVU89",
              "reaction": "angry"
            },
            {
              "user_id": "IJFG0F8GJ9",
              "reaction": "laughing"
            },
            {
              "user_id": "3F4V3NHVF43N8",
              "reaction": "thumbsup"
            },
            {
              "user_id": "VXDRTHVHM6D7",
              "reaction": "angry"
            },
            {
              "user_id": "A9SFDHSFDH78",
              "reaction": "laughing"
            },
            {
              "user_id": "DFSM8H87M9N",
              "reaction": "thumbsup"
            }
          ]
        }
      }
    ]
  }
]

Answer 1

Yes, a reactions table referring to the post, user, and their reaction is correct.

knex.schema.createTable('post_reactions', function(t) {
  t.integer('user').notNullable();
  t.foreign('user').references('id').inTable('users');
  t.integer('post').notNullable();
  t.foreign('post').references('id').inTable('posts');
  t.string('reaction').notNullable();

  t.unique(['user', 'post', 'reaction']);
}

The unique is so a user can only make a particular reaction once per post.

Getting the reactions to post 3 does not need a join.

knex('post_reactions')
  .select('user', 'reaction')
  .where('post', 3)

But maybe you want the user's names and the post's title.

knex('post_reactions')
  .join('users', 'users.id', '=', 'post_reactions.user')
  .join('posts', 'posts.id', '=', 'post_reactions.post')
  .select('posts.title', 'users.name', 'reaction')
  .where('post', 3)

---

I made it post_reactions both because they are reactions to a post (leaving room for reactions to something else), and because you may want to make a table for all possible reactions and join with that.

knex.schema.createTable('reactions', function(t) {
  t.increments();
  t.string('keyword').notNullable();
  t.unique('keyword');
}

knex.schema.createTable('post_reactions', function(t) {
  t.integer('user').notNullable();
  t.foreign('user').references('id').inTable('users');
  t.integer('post').notNullable();
  t.foreign('post').references('id').inTable('posts');
  t.integer('reaction').notNullable();
  t.foreign('reaction').references('id').inTable('reactions');

  t.unique(['user', 'post', 'reaction']);
}

Now we need an extra join if we want the reaction keyword in addition to the post's title and the user's name.

knex('post_reactions')
  .join('users', 'users.id', '=', 'post_reactions.user')
  .join('posts', 'posts.id', '=', 'post_reactions.post')
  .join('reactions', 'reactions.id', '=', 'post_reactions.reaction')
  .select('posts.title', 'users.name', 'reaction.keyword')
  .where('post', 3)

This has the advantage of ensuring every entry in post_reactions is a real reaction. Typos are not possible. It also means the keyword or other things about the reaction can change, but the reaction will still work.

Note that I avoided storing the reaction image itself in the database. You can do that, but it's often better to store assets on disk and refer to the file. This allows the assets to be directly linked to, reduces load on the database, and lets people work with the assets without having to touch the database.