Amazon DynamoDB single table; Get posts and users info in a single request

I am using Amazon DynamoDB for the database and am looking to go with a single table design.

My data types is Users, Posts, Categories, Comments, etc. I can get posts list but I need to get posts and user (author) data for each post (e.g firstName, lastName and etc)

Access patterns are expected to be as follows:

Fetch a user by its ID (Done)

• Get all users (Done with userStatusIndex)
• Get all posts by category Id (Done)
• Get all posts for a user: I can get the posts list with publishStatusIndex but I need author info and categories details, too. (Author info and categories details for each post)
• Get all posts for all users: (The above case)

I'm using Dynamoose:

const dynamoose = require("dynamoose");
const { Schema } = dynamoose;
   
const ServiceSchema = new Schema({
  pk: {
    type: String,
    hashKey: true,
  },
  sk: {
    type: String,
    rangeKey: true,
  },
  id: {
    type: String,
  },
  createdAt: {
    type: Date,
    default: new Date(),
    index: {
      global: true,
      name: "createdAtIndex",
    },
  },
  updatedAt: {
    type: Date,
    default: new Date(),
    index: {
      global: true,
      name: "updatedAtIndex",
    },
  },
  deleted: {
    type: Boolean,
    default: false,
  },
  firstName: {
    type: String,
  },
  lastName: {
    type: String,
  },
  username: {
    type: String,
    index: {
      global: true,
      name: "usernameIndex",
    },
  },
  userStatus: {
    type: String,
    enum: ["pending", "active"],
    index: {
      global: true,
      name: "userStatusIndex",
    },
  },
  categories: {
    type: Array,
    schema: [String],
  },
  title: String,
  body: String,
  publishStatus: {
    type: String,
    enum: ["pending", "published"],
    index: {
      global: true,
      name: "publishStatusIndex",
    },
  },

});

module.exports = dynamoose.model("service", ServiceSchema, {
  create: true,
});

Data (UPDATE: I decided to replace pk and sk: So now PK is USER#1234 and SK is POST#5678):

The response post that I need is something like it:

{
  id: '1234',
  title: 'Test',
  body: 'This is body',
  user: {
    id: '5678',
    firstName: 'David',
    picture: 'myImage.jpg',
    points: 12 // **This is very important. And it's the main issue. I need to change this value every day.**
  },
  categories: [
    { "id": "cat1", "name": "Sport" },
    { "id": "cat2", "name": "Cinema" }
  ]
}

UPDATE 1: This is a prototype of a social network.

UPDATE 2: I'm useing GraphQL. I also decided to replace pk and sk: So now PK is USER#1234 and SK is POST#5678.

NoSQL databases will often require you to denormalize your data to meet the access patterns of your applications. In other words, if you want this response:

{
  id: '1234',
  title: 'Test',
  body: 'This is body',
  user: {
    id: '5678',
    firstName: 'David',
    picture: 'myImage.jpg'
  },
  categories: [
    { "id": "cat1", "name": "Sport" },
    { "id": "cat2", "name": "Cinema" }
  ]
}

you'll want to store all of this information in the same partition.

I can see from your screenshot that you are storing the user ID along with the post. However, you are not storing the user information you need along with the post (e.g. first name and picture). To solve your problem, you have a few approaches to consider:

1. Store the users first name and picture with each post. This can be tricky if you have attributes that are mutable (change over time - like a users picture). When dealing with mutable data, you may want to consider a second option.
2. Make fetching posts a two step process: 1) fetch the post then 2) fetch the user details (both on the server side). This will mean you make 2 roundtrips to DDB, but that may be an acceptable trade-off in your application.
3. Get creative! You could store user profile pictures in a secure S3 bucket. Perhaps the key of the users profile picture can include immutable information about the user (e.g. the user ID, something that will never change). That way, a user can update their profile picture as much as they'd like. Your application would know that user with ID 1234 always has their latest profile picture in the S3 bucket labeled /your-app/user/1234-profile.jpg (for example)