1

I am new at DynamoDB, in my current project, I am trying to migrate most relational tables to Dynamo DB. I am facing a tricky scenario which I don't know how to solve

In Posgresql, 2 tables:

Student
id |  name  | age | address | phone 
---+--------+-----+---------+--------
1  | Alex   | 18  | aaaaaa  | 88888
2  | Tome   | 19  | bbbbbb  | 99999
3  | Mary   | 18  | ccccc   | 00000
4  | Peter  | 20  | dddddd  | 00000

Registration
id | class  | student | year 
---+--------+---------+---------
1  | A1     | 1       | 2018
2  | A1     | 3       | 2018
3  | A1     | 4       | 2017
4  | B1     | 2       | 2018

My query: 

select s.id, s.name, s.age, s.address, s.phone
from Registration r inner join Student s on r.student = s.id
where r.class = 'A1' and r.year = '2018'

Result: 

id |  name  | age | address | phone 
---+--------+-----+---------+--------
1  | Alex   | 18  | aaaaaa  | 88888
3  | Mary   | 18  | ccccc   | 00000

So, how can I design the dynamoDB table to achieve this result? in extend for CRUD

Any advice is appreciated

Phong Vu
  • 2,726
  • 6
  • 24
  • 52

1 Answers1

1

DynamoDB table design is going to depend largely on your access patterns. Without knowing the full requirements and queries needed by your app, it's not going to be possible to write a proper answer. But given your example here's a table design that might work:

          | (GSI PK) |
(P. Key)  | (Sort)   |                                 (GSI Sort)
studentId | itemType |  name  | age | address | phone | year 
----------+----------+--------+-----+---------+-------+------
1         | Details  | Alex   | 18  | aaaaaa  | 88888 |
1         | Class_A1 |        |     |         |       | 2018
2         | Details  | Tome   | 19  | bbbbbb  | 99999 |
2         | Class_B1 |        |     |         |       | 2018
3         | Details  | Mary   | 18  | ccccc   | 00000 |
3         | Class_A1 |        |     |         |       | 2018
4         | Details  | Peter  | 20  | dddddd  | 00000 | 
4         | Class_A1 |        |     |         |       | 2017

Note the global secondary index with the partition key on the item type and the sort key on the year. With this design we have a few query options:

1) Get student for a given id: GetItem(partitionKey: studentId, sortkey: Details)

2) Get all classes for a given student id: Query(partitionKey: studentId, sortkey: STARTS_WITH("Class"));

3) Get all students in class A1 and year 2018: Query(GSI partitionkey: "Class_A1", sortkey: equals(2018))

For global secondary indexes, the partition and sort key don't need to be unique therefore you can have many Class_A1, 2018 combos. If you haven't already read the Best Practices for DyanmoDB I highly recommend reading it in full.

Jacob Lange
  • 1,299
  • 14
  • 22
  • Hi Jake, thanks for your reply. I was far from my laptop last weekend, will check your suggestions, and get back with result soon. – Phong Vu Jun 04 '18 at 14:50
  • Hi Jake, after long, I tried your suggestion, it doesn't really return what I expected. For example: (3) get students in Class A1 and year 2018, it only returns the list of studentId, not student detail. Is there any way to get student details of Class A1 and year 2018? – Phong Vu Jun 05 '18 at 12:47
  • Yes you would receive a list of studentId's from 3), we have no joins in dynamo. At that point, what you do with those id's depends on your application. Perhaps call a [BatchGetItem](https://docs.aws.amazon.com/amazondynamodb/latest/APIReference/API_BatchGetItem.html) for the id's returned to get all the details at once. Or could GetItem as you go. – Jacob Lange Jun 05 '18 at 14:07
  • Alternatively if you want to limit reads on your table you could duplicate the minimal details for a student into each class map item, this way step 3) would return a subset of student detail along with the id. This would make reading the data cheaper but writing the data more expensive because if you change student details you have to update all the duplicate data. – Jacob Lange Jun 05 '18 at 14:11
  • It's worth it to learn how dynamodb works and whats the pricing model, here's a great stackoverflow answer on this: https://stackoverflow.com/questions/48219867/whats-the-recommended-index-schema-for-dynamo-for-a-typical-crud-application/48222907#48222907 – Jacob Lange Jun 05 '18 at 14:13
  • thanks Jake for the suggestions, I understand your points, I also had the same thoughts like you, just not sure if it's the right way to do it, because it's painful to query db like that. Keep looking for the best way for this issue. Jake, if you have any better idea, please share it here. Thanks a lot. In meanwhile, I also open a new question about Dynamo Db query: https://stackoverflow.com/questions/50702767/how-to-query-dynamo-db-properly – Phong Vu Jun 05 '18 at 14:37
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/172520/discussion-between-peter-pham-and-jake-lange). – Phong Vu Jun 05 '18 at 14:46