Suggestion : Update multiple items based on a common query

Question

One of the biggest difficulties I had was when I needed to model a many-to-many using the adjacency list pattern.  
  
Ok, you duplicate the data, the queries are fast and efficient, but what if I need to update the data and keep all replicas in sync? As far as I know, the API does not support doing a update with "begins_with" on the sort key.  
  
Basically I wanted to be able to do the following pseudo query :

```
UPDATE WHERE PK="user_001" AND SK="invoice_001" SET INVOICE_NAME="Invoice One"
```

In the end I realized that doing a batch update was far more expensive than simply doing two requests and emulating a SQL foreign key.

Accepted Answer

The idea with the Adjacency List Pattern is to distribute data that is immutable or does not change frequently.  What we are looking to produce are aggregations of items that are related to each other by querying either the partitions or reverse lookup index.  When you need to query for mutable attributes then you should setup the sort key so that the table/index query is delivering the entity item as well as relationship or edge items that are relevant to the query.  This pattern works very well with a directed graph. For undirected graphs full denormalization is often required, and when this occurs it is most common to fully duplicate items in some way which includes data that might be updated.  When this is the case the best option is to ensure the duplicated items can be efficiently queried and then use batchPutItem calls to clobber the duplicates.  This is usually more efficient than trying to update each one.  If the rate of change on the denormalized data is high then this is not the right pattern for the workload and we should look at optimizing for the write instead of the read.

Answer

Yes! This is to help maintaining replicated data updated.  
  
As Rick Houlihan masterfully explains it in his video here :    , to make queries more efficient you're often duplicating data in many-to-many relationships, but he never explains it how to maintain the replicas.  
  
And also I make a mistake in my example pseudo query, the correct way would be :

```
UPDATE WHERE PK="user_001" AND SK.BEGINS_WITH("invoice_") SET user_name="User one"
```

Answer

Thanks for the suggestion! To confirm I understand, you are looking to update a common attribute that is duplicated to multiple items that have the same partition key (or sort key?). Is that right?  
  
Edited by: ArturoAtAWS on May 9, 2019 9:25 PM

Answer

Thank you Rick, I understand your point of view.  
  
I just wish DynamoDB was more friendly/batteries-ready with common case scenarios and community/industry standards for developers.  
  
I like what the guys at FaunaDB are doing, they are taking a similar technology to DynamoDB and abstracting it as a "relational" database for the developers that don't care what's happening under the hood, but they still fall short on availability, documentation and pricing.  
  
I understand this might not be the direction DynamoDB wants to go, but it would make it more accessible and all-around for medium-sized projects that have a few or sometimes a single full stack developer.

Suggestion : Update multiple items based on a common query

Relevant content