Let's say that I am running a forum, where each user can make multiple posts.
So I structured my database the following way: (with unrelated columns omitted)
| PK | SK | STRING_0 |
|---|---|---|
| POST,post_id | thread,seq_num | user_id |
| USER,user_id | NOT_RELEVANT | big_meta_data |
A common use case is to render a whole discussion thread, with all the posts inside it. However, whenever we render a post, we will render some user meta data that is represented above as big_meta_data (This might include user's profile image, user's reputation, etc.)
We can of course first perform a query to get all posts, then for every user we see we perform an additional query (most probably batched).
Is there any way I can avoid the extra queries to the user records, or is the above good enough?
The only way I can think of is to keep a copy of that big_meta_data in the POST rows, but that seems hard to manage and consumes extra storage.
To get user data you'd need to do the additional query, considering it'll be single digit millisecond latency, it's quite normal to make multiple requests.
If you kept user information in each post item, and should a user change and attribute on their profile, you would need to change every post item they're stored it, which is complex and not recommended.