DynamoDB (NOSQL) database design

Question

I have following schema for tables in relational database.

Employees
- employee_id
- name
- email
- password
- is_manager

Projects
- project_id
- title
- description
- owner
- manager

Skills
- skill_id
- skill

EmployeeSkills
- employee_skill_id
- employee_id
- skill_id

ProjectMembers
- project_member_id
- project_id
- employee_id

I want to achieve same in NOSQL database design.

I am aware of creating key-pair values for first 3 tables(Employees, Projects, Skills).

Coming to the tables EmployeeSkills and ProjectMembers, each table contains information from 2 other primary tables. How to represent this in NOSQL database?

Should I create 2 new tables similar to RDBMS fashion or should I include this information as key-pair values for the attributes of primary tables? Please suggest.

Thanks in advance.

Answer 1

There can be multiple ways you can design your schema. First thing you should do before finalizing the schema would be to see what kind of query you will perform on your data.

Following is the primary schema I can think of, again you can modify this according to queries:

Employees
- employee_id
- name
- email
- password
- is_manager
- listOfProjects // There are list/map or Set data type which can be used
- listOfSkills 

Projects
- project_id
- title
- description
- owner
- manager

Skills
- skill_id
- skill

Data in listOfProjects would be the list of projectIds from your primary table.

In above schema, the advantage is that with single read you will have all the information of an employee. One disadvantage would be if you are going to search all employee having certain skill/projects then I would not recommend this schema.

If you are going to have those queries than have separate tables

    EmployeeSkills
    - skill_id  (hash)
    - employee_id (range)

   Create GSI of above table with
    - employee_id  (hash)   
    - skill_id  (range)

    ProjectMembers
    - project_id  (hash)
    - employee_id (range)

    Create GSI of above table with
     - employee_id (hash)
     - project_Id (range)

Edit Based on comment update the schema to support queries

Hope that helps