CODEWITHSUNDEEP
postgresqlamazon-web-servicesnosqlamazon-dynamodb
Phong Vu
Phong Vu

Reputation: 2896

how to migrate relational tables to dynamoDB table

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

Upvotes: 1

Views: 736

Answers (1)

Jacob Lange
Jacob Lange

Reputation: 1379

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.

Upvotes: 1

Related Questions