How to manage one-many mapping in DynamoDB

1
votes

I have a requirement to maintain one to many relationship data in AWS DynamoDB. e.g. One employee can have multiple skills mapped to him. In a typical RDBMS, I would create a SKILL_MASTER table with SkillId, SkillName as columns and an EMPLOYEE table. I will then map the SkillId to the EmployeeId in EMPLOYEE table. What would be the right approach in DynamoDB?

Note: I am totally new to NoSQL and hence this basic question.

2
nosqlamazon-dynamodb

2 Answers

4
votes

Dynamodb db doesn't support relationship like RDBMS, If you want reference like RDBMS stlye then you have to query multiple times based on skill id for an employee.

Lets take an example for implementing RDBMS approach in NoSQL (Not Recommended),

List of Records Skill Table

 { "skillId": 1, "name": "HTML" },
 { "skillId": 2, "name": "CSS" },
 { "skillId": 3, "name": "JS" }

List of records in Employee table with skillId

 { "employeeId ": 1, "name": "ONE", "skillId": [1,2,3] }

If your application frequently retrieves Employee data, you have query additionally to fetch skill details by skill id. It increases more complexity in development and also not recommended one.

Recommended Approach

An optimal approach is to include the skill info as a list in Employee data.

Employee record with skill list

{
    "employeeId": 1,
    "name": "ONE",
    "skill": [  //Adding skills as a list
        {
            "skillId": 1,
            "name": "HTML"
        },
        {
            "skillId": 2,
            "name": "CSS"
        }
    ]
}

By adding skills in Employee table, you can retrieve Employee details with the list of skills in single query.

6
votes

At first, you should have a look on DynamoDB's Adjacency List Design Pattern https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/bp-adjacency-graphs.html

if you only want to have a one-to-many relation, you can design the table like this.

The sort key here, indicates the type of the record.
If the record is employee, the SK start with the prefix 'employee-' and its id.
If the record is skill, the SK can be 'skill-' with its id, or if you don't want to create id for it, you can just use 'skill' string simply.

base table
+------------+------------+-------------+
|PK          |SK          |Attributes   |
|------------|------------|-------------|
|employee-id |employee-id |name         |
|employee-id |skill-id    |name         |
+------------+------------+-------------+

also if you want to search for all employees with specific skill, you should create other GSI table. set the skill's name as partition key of GSI and project on your base table's partition key and sort key

+------------+------------+
|GSI-PK      |Projection  |
|------------|------------|
|name        |PK, SK      |
+------------+------------+

Then you can query the one employee's all skills by using SELECT * FROM base_table WHERE PK=employee-123 AND SK.startsWith('skill-')
if you want to get employee's id by skill id, you can use SELECT * FROM GSI_table WHERE name='HTML'

Also, if you really want to get all employees who have the same skill, it would be better to have a many-to-many relationship. For this you can refer to my other answer about many-to-many in DynamoDB: How to handle many to many in DynamoDB

NOTICE: the SQL I wrote is just pseudo code, to provide you an idea only.