1

I have a single table design in Dynamodb where I model the relationship between 2 items, users and posters. A user may have many posters associated with them (0-N), whereas a poster may only have one or no users (0-1).

I want to mainly query by user: get the user and all associated posters, hence I want the user and the user-poster associations to share the same PK. When I associate a poster with a user, I don't want to make the association if the poster is associated with another user already.

PK    SK             GSI_PK             GSI_SK
u1    user#u1        USER               <dtg>
u2    user#u2        USER               <dtg>


u1    pa#p1          POSTER_ASSOCIATION <dtg>
u1    pa#p2          POSTER_ASSOCIATION <dtg>       

p1    poster#p1      POSTER             <dtg>
p2    poster#p2      POSTER             <dtg>

u1 is user 1, p1 is poster 1, the pa in the SK refers to poster association, <dtg> is a date-time group. User 1 has posters 1 & 2 associated, user 2 has no posters.

I can query all the user data by querying PK = u1 AND begins_with(#SK, "user#"), or the user and all associated posters via just PK = u1.

How do I formulate a put request such that if I try to associate poster 1 with user 2 that the attempt fails?

John
  • 10,837
  • 17
  • 78
  • 141
  • Are you asking for it to behave like a [relational database](https://stackoverflow.com/questions/36753861/how-to-join-tables-in-aws-dynamodb)? – lloyd Jun 28 '20 at 22:02
  • No, I'm wondering what the best way to model a one to many relationship is in DDB, where the relationship is exclusive, i.e. an object may not be owned by more than one entity. I think it requires 2 queries (first to confirm whether the relationship exists, the second to write), but I'm interested if there's a way of doing this whereby a single query insert is possible. – John Jun 28 '20 at 22:43
  • If you're happy with [Eventual Consistency](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/HowItWorks.ReadConsistency.html) you can trigger a Lambda to cleanup the inconsistencies or cron job or write your get queries to only return based on the timestamp such as the earliest record wins. If [Availabilty](https://www.researchgate.net/figure/Consistency-versus-Availability-Trade-off-During-Updates_fig2_288363019) is less important then perhaps use RDS or use strongly consistent reads to attempt to prevent this. – lloyd Jun 28 '20 at 23:24
  • [Graph](https://aws.amazon.com/blogs/big-data/building-a-graph-database-on-aws-using-amazon-dynamodb-and-titan/) might be an option – lloyd Jun 28 '20 at 23:27

0 Answers0