DynamoDB many-to-many equivalent

1
votes

I want to replicate the following relations in DynamoDB

A user can have many medals

A medal can be granted to many users

I read that you should only use a single table in DynamoDB, but I haven't thought of a solution to the problem yet.

My first thought was to create a medals table, and then in the users table, let the users have a list of medal IDs.

This requires more requests to the database and still doesn't sound good.

Any advice?

2
amazon-web-servicesamazon-dynamodb
I think that this post will answer your question: stackoverflow.com/questions/48537284/… – Tommy
@mxmissile why couldn't a Global Secondary Index on the users table handle the "all users with a gold medal" query? – Mark B
Possible duplicate of Amazon DynamoDB and relationship many-to-many – JD D

2 Answers

2
votes

Go look at the adjacency list pattern for many to many relationships in DynamoDB. That should help you out. Also go check out Rick Houlihan's advanced data modeling talks at 2017 and 2018 reInvent.

0
votes

Relational Database

For a simple use case on a regular Relational Database settings this can be achieved with:

Users

ID  | Name
----+---------------
U01 | John
----+---------------
U02 | Richard


Medals

ID  | Name        | Points
----+-------------+--------
M01 | Gold        | 500
----+-------------+--------
M02 | Sliver      | 200


Users_Medals

User ID  | Medal ID | Attainment
---------+----------+-------------
U01      | M01      | Apr 2, 2019
---------+----------+-------------
U01      | M02      | Jun 3, 2019
---------+----------+-------------
U02      | M02      | Jun 2, 2019

DynamoDB

However with DynamoDB, the objective is to flatten out data for fast retrieval (single digit milliseconds) via the Hash Key (think of it as a Primary Key), there are strictly no joins across tables in DynamoDB

Data is slightly duplicated (i.e. Medal Name is duplicated for each user) which is likely to involve a heavy penalty during update, delete (i.e. if Medal Name needs to be changed from 'Gold' to 'Platinum' later, all records need to be scanned for and updated individually)

<!-- Hash and Range Key -->

Users_Medals

User ID (HashKey)  | Medal ID (Range Key) | Medal Name  | Attainment
-------------------+----------------------+-------------+--------------
U01                | M01                  | Gold        | Jun 3, 2019
-------------------+----------------------+-------------+--------------
U01                | M02                  | Silver      | Jun 2, 2019
-------------------+----------------------+-------------+--------------
U02                | M02                  | Gold        | Apr 1, 2019


<!--- Hash Key Only --->

Users_Medals

UserID_MedalID (HashKey) | Medal Name  | Attainment
-------------------------+-------------+--------------
U01#M01                  | Gold        | Jun 3, 2019
-------------------------+-------------+--------------
U01#M02                  | Silver      | Jun 2, 2019
-------------------------+-------------+--------------
U02#M02                  | Gold        | Apr 1, 2019

Aim to structure your tables and application data for fast retrieval, hope this gives you an idea

Updates

If your Application has a requirement to display or get list of Medals, then a separate Standalone Medals Table should be used

Medals

ID (Hash Key)  | Name        | Points
---------------+-------------+--------
M01            | Gold        | 500
---------------+-------------+--------
M02            | Sliver      | 200
---------------+-------------+--------
M03            | Bronze      | 200

To get a complete list of Medals, a Scan is needed

To get the details of a Medal (i.e. Name, Points) by the ID (Hash Key), Get Item should be used