Best practice - defining partition and sort keys

[michael-pont]

I noticed the following differences when calling update operations on an entity, based on how I defined a sort key for a GSI index:

All update operations I run work with the below code as long as I pass id in the update body. I can even update clientId or dept
in separate calls if I want to.

@Entity<UserEntity>({
  name: 'user',
  // Query on user id
  primaryKey: {
    partitionKey: 'USER#{{id}}',
    sortKey: 'USER#{{id}}',
  },
  indexes: {
    // Query on user email possible as well
    GSI1: {
      partitionKey: 'USER#{{email}}',
      sortKey: 'USER#{{email}}',
      type: INDEX_TYPE.GSI,
    },
    // Query on clientId and can also query on dept
    // For example query clientId = 1 and then beginsWith DEPT#marketing
    GSI2: {
      partitionKey: 'CLIENT#{{clientId}}',
      sortKey: 'DEPT#{{dept}}',
      type: INDEX_TYPE.GSI,
    },
  },
})

This behavior changes once I change the following lines to

GSI2: {
   partitionKey: 'CLIENT#{{clientId}}',
   sortKey: 'CLIENT#{{clientId}}#DEPT#{{dept}}',
   type: INDEX_TYPE.GSI,
},

Now when I try to update just clientId for example, then I get a resolver error

"dept" was referenced in CLIENT#{{clientId}}#DEPT#{{dept}} but it's value could not be resolved.

And of course if I just want to update dept by itself, I get the same resolver error:

"clientId" was referenced in CLIENT#{{clientId}}#DEPT#{{dept}} but it's value could not be resolved.

The first approach makes updating key attributes much easier (for 2nd approach, if I want to update either clientId or dept, I always have to supply them together). I can also query
something like the following to get all users belonging to a specific department for a client:

const dept= await getEntityManager().find(User,
  {clientId 'clientId-1', queryIndex: 'GSI2'}, {
  keyCondition: {
    BEGINS_WITH: 'DEPT#marketing',
  },
})

I can also do the same with the 2nd approach, just the BEGINS_WITH conditions would have to begin with CLIENT#clientId-1#DEPT#marketing

My question is what approach makes more sense? Is the behavior I describe expected?

[whimzyLive]

Hi @michael-pont,

Sorry for the late response.

The first approach is preferred because it will provide you with the most flexibility when updating data, while also letting you query the departments for a given client.

The second approach will always require you to pass in all the referenced params when updating.

[michael-pont]

@whimzyLive Thanks! That makes sense. As I started using this library, I didn't have a solid command of single table design so my selection of PKs and SKs had some flaws, but now I understand what is going on.