I am just new in NoSQL and DynamoDB. I try to moving from using AWS RDS to DynamoDB.
I have table user which can have more than one of work experiences and can have more than one of interest (table of interest have master data in interest table). There will be a lot query filtering by user by their work experiences and interest. Example: Find user who have work experiences title is 'Programmer' or find user who have interest is 'SQL, NoSQL'.
User Table: - user_id (PK) - name - birthdate - ... Work Experience Table: - user_id (PK) - work_sequence_id (PK) - work_title - work_description Interest Table: - interest_id (PK) - interest_name User Interest Table: - user_id (PK) - interest_id (PK)
So I try to convert it to DynamoDB:
User Table: - user_id (PK/Partition Key) - name - birth_date - ... Work Experience Table: - user_id (PK) - work_sequence_id (SK/Sort Key) - work_title (global secondary index) - work_description
For the interest table, I want to re-structure the table. Basically, beside filtering user by interest, I just want to get the most popular interest for suggestion word (filter by word that contain 'search_word'). Example if there is 7 user have interest in 'SQL' and 10 user have interest in 'NoSQL'. When user search the word 'SQ' the word 'NoSQL' will appear first and 'SQL' will appear second.
So I create like this:
User Interest Table: - user_id (PK) - interest_name (SK) Interest Table: - interest_name (PK) - count (SK)
Then I will use the count to get the popular interest.
Usually I use the join to get user data, work experiences, and interests also using aggregated function to get most popular interest. Now I need to use four query operator to get all the data. Basically, I need efficiently and effectively store the data in term of cost.
Do you have any database design suggestion for this scenario in DynamoDB?