By using AWS re:Post, you agree to the AWS re:Post Terms of Use
AWS re:Postre:Post

DynamoDB many to many relationship - best approach

0

I am doing a single table design. These are the entities that are related to my question: KITCHEN, MENUITEM, USER, ORDER, and FAVOURITE.

**Relationships: **

one to many: KITCHEN can have many MENUITEM. https://ibb.co/N95p5rM (screenshot)

one to many: USER can have multiple ORDERS. https://ibb.co/RpMdtQL (screenshot)

many to many: USER can have multiple FAVOURITE KITCHENS and a KITCHEN can have multiple USERS make it as FAVOURITE. This is the one that I am struggling to implement it.

My access pattern should be query to get a particular user with all the kitchens that are favorites. So the user is a partition key, because I want to query by the user.

Topics
Database
Tags
Amazon DynamoDB
Language
English
AWS-User-6042351
asked 2 years ago1391 views
1 Answer
0

I suggest you have a PK of <user> and an SK of FAVORITEKITCHEN#<kitchen> and then you can fetch all the favorites for that user with one query using a begins-with on the SK. Note this will fetch the kitchen id and can fetch any other kitchen metadata that you choose to denormalize into the favorite kitchen item. If you're thinking "But I don't want to duplicate kitchen metadata into every user favoriting that kitchen" then your alternative is to fetch the metadata with a second batch-get-items. Denormalizing works better at scale.

AWS
jzhunter
answered 2 years ago
  • AWS-User-6042351
    2 years ago

    Thank you for your answer. Your first solution I didn't use it because of the duplication as you stated. Your second solution might be a better approach. However, it contradicts the main reason of using a single table design, which is retrieving all the required data in a single request. But is there any other solution? And which one do you think is the best practice, first or second solution?

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions

Relevant content