1

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?

  • Instead of trying to use tables for the user interest and experience data turn them into map columns in user table. You can then store that data as JSON for each user. This has some useful info on querying the map fields for your aggregate. AWS forum You also may want to look at storing the counts of each interest in a second table that gets updated as users add and remove them. While you can scan the table that could get expensive depending on how often and the number of rows you have. – Aaron Jun 6 at 14:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.