In my previous article, I’ve described some of the AWS DynamoDB data modelling best practices. In this article, I will continue data modelling with specific focus on handling relationship between different entities of the data. Despite the fact that NoSQL databases don’t support any joins like relational databases, these relationships have to be handled carefully in NoSQL databases. The design of relationships may have a lot of performance consequences. In the last article, we talked about the application access patterns. The reason I’ve mentioned “access patterns” here again is because access patterns will have to be considered while designing relationships.
We have already listed down all the entities and respective relationships over a paper or some software ( mentioned in last article). To remind again, DynamoDB recommends to use a single DynamoDB table design for almost all of the entities in the application. Let us start first with entities with no relations followed by 1:1, 1:M and M:N relations.
Entities with no relations
We already have a list of attributes (from last article) (i.e. primary key or other attributes combined) which identify the rows of that table uniquely. Using the Create DynamodB table interface over AWS console ( as shown in Fig 1 below), one can create a table. In most of the cases a developer may not have direct access to AWS console. He will have to use AWS SDK or AWS CLI to create DynamoDB tables. While creating a table user will have an option to enter the name of the primary key field and sort key field. Now, as the target is to use single table for all the entities, one should use generic name for this field rather than a specific name to a specific entity. For example, the most widely used name is “PK” or “SK”, abbreviations for primary key and sort key respectively. You read it right, the field names are in upper case. For example, let us try to fit in orders, products and categories entities using this approach. Assuming the field name as PK and SK, the possible values for orders, products and categories could be ORDERS#<order_id>, PRODUCTS#<prod_id>, CATEGORY#<category_id>. Please replace (***) in the ****#<***_id> to get a desired PK value for respective entity. This way, our independent tables (from relational design) representing different entities have been fit into single table design.
Please note that this is the first and important step to have all the entities embedded in a single table design. The “how to query” part for this design will be covered in the next article.
Fig. 1
Entities with 1:1 relationships
In the previous step, our single table without relations is ready. However, let us take it further to handle 1:1 relations. If there are entities with direct 1:1 relations, simply create a new field like relatedPK. This new field can be stored to have 1:1 relations between such entities.
Entities with 1:M relationships
Now, coming to 1:M relations, there are two parts. One entity has multiple values referring to other entity. However, the entity it is referring to can have only one record to the referred table. Let us try to simplify it using an example of employees and a department. One department can have multiple employees, however, one employee can belong to only one department. This is an example of 1:M relations. For these two entities, we will have values for PK ( considering PK uniquely describes a record, else use SK as per your use case) as DEPT#<dept_id> and EMP#<emp_id>. We can have an additional field dept_id while storing employees data. This will give us a solution to 1:M relations. The querying of these relations will be handled in my next article.
Entities with M:N relationships
Let us move to M:N relations now. One of the approach is to have PK value of these entities as SK value of related/other entity. If this PK value can not be specified in SK field of related entity, then a new field ( example field name could be “relatedPK”) can be created. This will resolve our purpose of handling the M:N relations. You may also like a wonderful article in the DyanmoDB documentation at this link. Querying of these relations along with other parts of this article will be discussed in next article.
Conclusion
This article covers how to handle relationships in the DynamoDB database. In the next article I’ll touch upon “Querying best practices”.
