I know writing queries in dynamodb but I am still new. Now for a project rather than simply creating a table with a partition key and sort key, I have designed a no-sql data-model data structure for my table. Accordingly, I have implemented list/add/update/delete dynamodb queries.
My Question is how to query all records, for example query all products. For the Admin panel of my application, I need to display all the records of an entity type, for each type of Entity.
I know querying all records has been basically asked before at Retrieve all items from DynamoDB using query?.
However, I am posting this question of doing this without scan operation; how to do in terms of or in the light of considering a proper/complete nosql design. Specifically, I am asking because I am currently using a static value for partition key.
I have followed single table design( in any case, the recommendation is most well-designed applications use only 1 table ). As part of this, at this link https://www.trek10.com/blog/dynamodb-single-table-relational-modeling/ it is recommended to use a static value for sort key, which will become primary key of (reverse lookup) Index. Thereby allowing querying all "Products", for example.
My application, will have "Categories". It will also have "Surveys" created, with each Survey belonging to a Category. For the admin panel, I need to display all Categories and also all Surveys.
What I have done currently:
I) Table Design:
table : tbl_surveys
Partition Key: tbl_pk_surv
Sort Key:tbl_sk_surv
One "Data" Attribute: data_attr
I created a GSI with tbl_sk_surv as Partition Key and data_attr as Sortkey.
II) For example, for Categories, Category records would look like this:
tbl_pk_surv | tbl_sk_surv | data_attr | cat_name | status
Cat-1VqNAF3Vi1 | ADMIN_CATEGORY | 1565673999 | Cat1 | 1
Cat-QUTQHQwd42 | ADMIN_CATEGORY | 1565950613 | Cat2 | 1
I am querying GSI with 'ADMIN_CATEGORY' as the value for GSI Hash key(tbl_sk_surv).
Please let me know how else this could be done. In general, please provide guidance on nosql data-modelling.
I have followed following documentation for nosql design
https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/bp-general-nosql-design.html
https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/bp-modeling-nosql.html
https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/bp-modeling-nosql-B.html
https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/bp-adjacency-graphs.html#bp-adjacency-lists
https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/bp-relational-modeling.html
https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/bp-gsi-overloading.html
https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/bp-partition-key-design.html
https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/bp-sort-keys.html
https://www.trek10.com/blog/dynamodb-single-table-relational-modeling/
https://aws.amazon.com/blogs/database/using-sort-keys-to-organize-data-in-amazon-dynamodb/
Update:
On more searching, I find that the below question is similar to mine. But I am asking more specifically, because I am using GSI. Whichever way, please guide:
How to design key schema to have only one DynamoDB table per application?
