CODEWITHSUNDEEP
databaseamazon-web-servicesnosqlamazon-dynamodb
ajax992
ajax992

Reputation: 996

How to model One-To-Many relationships in DynamoDB such that all child relationships can be queried?

One common strategy for one-to-many relationships in DynamoDB is using a composite primary key; a broader Partition key as parent, and a narrower Sorting key that contains child relationships. The following example is taken from this article by data scientist Alex Debrie:

Primary Key

This strategy solves the most common retrieval use cases:

The use case I am trying to solve does not seem to be covered by this model. What if you wanted to retrieve all the metadata from all organizations combined? Or what if you wanted to know all the users across organizations? Even by using a Global Secondary Index, you have no choice but to split all fields into their own partition; this requires a scan to retrieve. Does dynamo have any features to facilitate these kind of retrievals?

Upvotes: 1

Views: 423

Answers (1)

Dennis Traub
Dennis Traub

Reputation: 51624

  1. To retrieve the metadata for all organizations, you can model it the following way: The SK for the respective metadata items doesn’t need to contain the company name. Just give it the value “META” for every company. Create a GSI that uses the SK as PK. This way you can query all items from the index using the PK “META”.

  2. To retrieve all users across organizations, add an attribute “type” with the value “USER” to all user entries. Create a GSI with this type as PK. This way you can query all users from the index using the PK “USER”.

Upvotes: 2

Related Questions