Modeling Relational Data in DynamoDB (nested relationship)

Question

Entity Model:

I've read AWS Guide about create a Modeling Relational Data in DynamoDB. It's so confusing in my access pattern.

Access Pattern

+-------------------------------------------+------------+------------+
| Access Pattern                            | Params     | Conditions |
+-------------------------------------------+------------+------------+
| Get TEST SUITE detail and check that      |TestSuiteID |            |
| USER_ID belongs to project has test suite |   &UserId  |            |
+-------------------------------------------+------------+------------+
| Get TEST CASE detail and check that       | TestCaseID |            |
| USER_ID belongs to project has test case  |   &UserId  |            |
+-------------------------------------------+------------+------------+
| Remove PROJECT ID, all TEST SUITE         | ProjectID  |            |
| AND TEST CASE also removed                |   &UserId  |            |
+-------------------------------------------+------------+------------+

So, I model a relational entity data as guide.

+-------------------------+---------------------------------+
|       Primary Key       |            Attributes           |
+-------------------------+                                 +
|     PK     |     SK     |                                 |
+------------+------------+---------------------------------+
|   user_1   |    USER    |    FullName    |                |
+            +            +----------------+----------------+
|            |            | John Doe       |                |
+            +------------+----------------+----------------+
|            |   prj_01   |   JoinedDate   |                |
+            +            +----------------+----------------+
|            |            | 2019-04-22     |                |
+            +------------+----------------+----------------+
|            |   prj_02   |   JoinedDate   |                |
+            +            +----------------+----------------+
|            |            | 2019-05-26     |                |
+------------+------------+----------------+----------------+
|   user_2   |    USER    |    FullName    |                |
+            +            +----------------+----------------+
|            |            | Harry Potter   |                |
+            +------------+----------------+----------------+
|            | prj_01     |   JoinedDate   |                |
+            +            +----------------+----------------+
|            |            | 2019-04-25     |                |
+------------+------------+----------------+----------------+
| prj_01     | PROJECT    |      Name      |   Description  |
+            +            +----------------+----------------+
|            |            | Facebook Test  | Do some stuffs |
+            +------------+----------------+----------------+
|            | t_suite_01 |                |                |
+            +            +----------------+----------------+
|            |            |                |                |
+------------+------------+----------------+----------------+
| prj_02     | PROJECT    |      Name      |   Description  |
+            +            +----------------+----------------+
|            |            | Instagram Test | ...            |
+------------+------------+----------------+----------------+
| t_suite_01 | TEST_SUITE |      Name      |                |
+            +            +----------------+----------------+
|            |            | Test Suite 1   |                |
+            +------------+----------------+----------------+
|            | t_case_1   |                |                |
+            +            +----------------+----------------+
|            |            |                |                |
+------------+------------+----------------+----------------+
| t_case_1   | TEST_CASE  |      Name      |                |
+            +            +----------------+----------------+
|            |            | Test Case 1    |                |
+------------+------------+----------------+----------------+

If I just have UserID and TestCaseId as a parameter, how could I get TestCase Detail and verify that UserId has permission.

I've thought about storing complex hierarchical data within a single item. Something likes this

+------------+-------------------------+
| t_suite_01 | user_1#prj_1            |
+------------+-------------------------+
| t_suite_02 | user_1#prj_2            |
+------------+-------------------------+
| t_case_01  | user_1#prj_1#t_suite_01 |
+------------+-------------------------+
| t_case_02  | user_2#prj_1#t_suite_01 |
+------------+-------------------------+

Question: What is the best way for this case? I appreciate if you could give me some suggestion for this approach (bow)

Rick Houlihan · Accepted Answer · 2019-04-19T01:28:44.100

7

I think the schema below does what you want. Create a Partition Key only GSI on the "GSIPK" attribute and query as follows:

Get Test Suite Detail and Validate User: Query GSI - PK == ProjectId, FilterCondition [SK == TestSuiteId || PK == UserId]
Get Test Case Detail and Validate User: Query GSI - PK == TestCaseId, FilterCondition [SK = TestSuiteId:TestCaseId || PK = UserId]
Remove Project: Query GSI - PK == ProjectId, remove all items returned.

Queries 1 and 2 come back with 1 or 2 items. One is the detail item and the other is the user permissions for the test suite or test case. If only one item returns then its the detail item and the user has no access.

edited Apr 19 '19 at 01:28

answered Apr 18 '19 at 21:21

Rick Houlihan

246
1
3

1

Thanks @Rick Houlihan. This is what I'm waiting for. I didn't know we can use PK and SK in GSI table for filter. It's really really helpful. You save my job by the way – Long Nguyen Apr 19 '19 at 05:01
BTW, If I want to create a table like you created, where I can find it. Thanks you. – Long Nguyen Apr 19 '19 at 05:05
1

The modeler I use to generate those views can be found here: http://rh-web-bucket.s3-website-us-east-1.amazonaws.com/ It lacks some flow control and error handling and tends to fail silently when it does not get the proper input, but its a useful tool. You can load data from JSON files, DynamoDB Tables, MySQL and MS-SQL. To load anything other than JSON use the jar and follow the directions on the page. Here is a link to download the JSON file I used for the tables shown above: https://s3-us-west-2.amazonaws.com/rh-public/Users.json – Rick Houlihan Apr 19 '19 at 23:40
Brilliant. I wonder why AWS doesn’t launch something like this for user – Long Nguyen Apr 20 '19 at 00:45
Let me copy json file here to prevent file from being deleted – Long Nguyen Apr 22 '19 at 02:24
[{"tableSpec":{"partition_key":"PK","sort_key":"SK","sortkey_datatype":"string","name":"User Access"},"gsiSpec":[{"partition_key":"GSIPK","name":"GSI1"}]},{"_row":1,"PK":"User_1","SK":"User_1","name":"Frances Mellows","login":"fmellows0"}, {"_row":1,"PK":"User_1","SK":"Project_1","name":"Cardguard","role":"User","GSIPK":"Project_1"}, {"_row":1,"PK":"User_1","SK":"Project_1:TestSuite_1","role":"Editor","GSIPK":"Project_1"}, {"_row":1,"PK":"User_1","SK":"Project_1:TestSuite_1:TestCase_1","role":"User","GSIPK":"TestCase_1"}, – Long Nguyen Apr 22 '19 at 02:25
{"_row":1,"PK":"User_1","SK":"Project_1:TestSuite_1:TestCase_2","role":"Editor","GSIPK":"TestCase_2"}, {"_row":1,"PK":"Project_1","SK":"Project_1","name":"Cardguard","GSIPK":"Project_1"}, {"_row":1,"PK":"Project_1","SK":"TestSuite_1","name":"Suite 1","GSIPK":"Project_1"}, {"_row":1,"PK":"Project_1","SK":"TestSuite_1:TestCase_1","description":"nisi at nibh in hac habitasse platea dictumst aliquam","GSIPK":"TestCase_1"}, {"_row":2,"PK":"Project_1","SK":"TestSuite_1:TestCase_2","description":"in sagittis dui vel nisl duis ac","GSIPK":"TestCase_2"}] – Long Nguyen Apr 22 '19 at 02:25
what if we have to update name field of Project_1 in this model. Should we update it for each and every user in the project also(for eg. PK: User_1, SK: Project_1 - name field) – Anil Sivadas Mar 12 '20 at 08:06

score 1 · Answer 2 · answered Apr 17 '19 at 21:32

The first question you should ask is: why do I want to use key-value document DB over relational DB when I clearly have strong relations in my data?

The answer might be: I need a single-digit millisecond queries at any scale (millions of records). Or, I want to save money using dynamodb on-demand. If this is not the case, you might be better with a relational DB.

Let’s say you have to go for dynamodb. If so, most of patterns applicable for relational DBs are anti-patterns when it comes to NoSQL. There is a useful talk from last re-invent about design patterns for dynamodb and advice to watch it https://youtu.be/HaEPXoXVf2k.

For your data I’d think about taking similar approach, and having two tables: users and projects.

Projects should store sub-set of test suits as map of array of objects and test cases as map of array of objects. Plus you could add list of user ids in the map of strings. Of course you will need to maintain this list when users join or leave the project/s.

This should satisfy your access patterns.

Thanks Alex. I’ve watch above re:invent 2018 many times before making this ticket on StackOverFlow. Firstly their suggestion is One application service = one table. Secondly, for explanation I made user record for clearly, my application’s user table is AWS Cognito. Their example seems like not clear in my case, they suggested that store a pair partition key - sort key, instead of sub-set. — Long Nguyen, Apr 17 '19 at 23:25

Modeling Relational Data in DynamoDB (nested relationship)

2 Answers2

Linked