When using DynamoDB (or other NoSQL Key-value DB) and based the modeling on a Single-Table Design, we can have something like this to map users and the user job title:
So in case one of my Access Patters is "Get the current job title and user info per username", I want to show the user info and the current job title name (for simplification I don't care previous job title, etc.).
I can query/get the USER- and as I defined the job-title:name as an attribute, I can show with only one query. So far, so good.
What happens if the job title name is updated? As DynamoDB or any NoSQL DB doesn't have an UPDATE WHERE clause, do I need to get all the users with that job title and update them one by one?
I read some other questions or articles that instead of defining the attribute job title name inside the USER, keep only the job title ID, but that is using NoSQL as RMDB; as I will need from my app, get USER (one query), get the job title (another query) and do the JOIN later in the app, which I think is not the intention of DynamoDB.
So Is it "OK/fine" whenever the job title changes, get all the users of that job title (can be done with the SK), and update one by one?
DynamoDB is an OLTP system, where it can handle high throughput with low latency. As you mention doing joins usually defeats the purpose of the system. If you don't expect the attribute like jobTitle to change too often, then it is usually done by acquiring all the items and updating them individually.
However, if you expect an attribute to be updated often, it would make more logical sense to have a single item which holds the info, and a pointer to that item like JobId. It would then become an adjacency list pattern.
