在DynamoDB中建模关系数据（嵌套关系） [英] Modeling Relational Data in DynamoDB (nested relationship)

问题描述

实体模型：

我已阅读有关创建

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)

解决方案

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

1. Get Test Suite Detail and Validate User: Query GSI - PK == ProjectId, FilterCondition [SK == TestSuiteId || PK == UserId]

2. Get Test Case Detail and Validate User: Query GSI - PK == TestCaseId, FilterCondition [SK = TestSuiteId:TestCaseId || PK = UserId]

3. 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.

这篇关于在DynamoDB中建模关系数据（嵌套关系）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！