Sign inJoin
3
6 Comments

DynamoDB data modeling question

by Martin

Hey Hackers!

I'm currently making the mental switch from SQL to NOSQL and I'm having trouble modeling the DynamoDB database schema above effectively.

The idea for the data schema above is: each user can have zero or more posts, each post can have zero or more comments.

What would you say is the most effective model for the schema above?

Ideally I'd like to achieve this without using any global secondary indexes if possible.

Any recommendations to push me in the right direction are appreciated.

Cheers,

Martin's avatarMartin
on May 30, 2020
Say something nice to iamonlyanegg…
Post Comment
  1. 1

    I've been reading through Alex Debrie's dynamodb book and what he suggests is absolutely key to modeling is knowing your access patterns -- in what ways are you going to be retrieving this data?

    dnissley's avatar dnissley
    ·
    6 years ago
    ·
    Reply
    1. 1

      Good question. Queries are:

      • get all of a user's posts
      • get all of a post's comments
      Martin's avatar Martin
      ·
      6 years ago
      ·
      Reply
      1. 2

        In that case, assuming single table design, I'm thinking:

        Storing Users:

        PK = USER::<id>
SK = Overloaded with METADATA and POST::<id>

        Storing Posts:

        PK = POST::<id>
SK = Overloaded with METADATA and COMMENT::<id>

        So let's say you have a user with one post and one comment, then you'd have the following records:

        PK: USER::1, SK: METADATA, email: user@example.com, ...
PK: USER::1, SK: POST::1, title: abc, content: xyz, ...
PK: POST::1, SK: METADATA, title: abc, content: xyz, ...
PK: POST::1, SK: COMMENT::1, text: hello, ...
        dnissley's avatar dnissley
        ·
        6 years ago
        ·
        Reply
        1. 1

          Nice! Thanks, that does solve it for the mentioned data access patterns. I actually came to a slightly different solution that would let me retrieve a wider query (if needed) in which I could retrieve all of a users posts PLUS all of the comments with one query as follows:

          PK: USER::<id> SK = Overloaded with META | POST::<id> | POST::<id>::COMMENT::<id>

          Following your example:

          1. PK: USER::1, SK: METADATA            -> email: user@example.com, ...
2. PK: USER::1, SK: POST::1             -> title: abc, content: xyz, ...
3. PK: USER::1, SK: POST::1::COMMENT::0 -> title: abc, content: xyz, ...

          It's also more compact resulting in one less item (in this 1 user/1 post scenario).

          Martin's avatar Martin
          ·
          6 years ago
          ·
          Reply
          1. 2

            Although my solution requires knowing the user id as well to retrieve a post metadata.

            Martin's avatar Martin
            ·
            6 years ago
            ·
            Reply
            1. 1

              Nice, seems like a reasonable trade off.

              dnissley's avatar dnissley
              ·
              6 years ago
              ·
              Reply