Inverted index, GSI overloading, and Sparse index in DynamoDB

Additional access patterns in DynamoDB data modeling.

Photo by Andreas Weiland on Unsplash

In my previous article about DynamoDB data modeling, I mentioned three methods that we can use to determine the secondary indexes if need.

1. Inverted Index
2. GSI Overloading
3. Sparse Index

Now I’ve decided to have an in-depth look at these methods using an example and explain the scenarios under which we can employ each method.

Inverted Index

Photo by processingly on Unsplash

Inverted indexes are used to model many to many relationships in DynamoDB data modeling. Let's take the same example as the previous article.

We can see that there is a many to many relationship between Employee and Project entities which means a single employee can work in multiple projects and a single project contains multiple employees. We can further break this relationship follows,

Now there are two new ones to many relationships instead of many to many relationship and we have a new entity as well. Let's take a scenario (access pattern) where we need to find all projects an employee is working on. We can use the newly created ProjectEmployee entity for this. So I have chosen the following partition key and sort key for the ProjectEmployee entity.

Entity Name: ProjectEmployee
Partition Key: COM<companyId>#PRO#<projectId>
Sort Key: COM<companyId>#EMP#<employeeId>

Using the above primary key one side of the many to many relationship can be queried very easily. We can get an employee list of a certain project easily using that partition key(sort key can be left empty). But when it comes…